package com.mairuide.bill;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.base.PageSupport;
import com.mairuide._frame.config.CacheableConfig;
import com.mairuide._frame.utils.StringUtils;
import com.mairuide._frame.utils.Testtrans;
import com.mairuide.bill.billcash.BillCashDomain;
import com.mairuide.bill.billstatus.BillStatusDomain;
import com.mairuide.bill.information.PersonCards;
import com.mairuide.bill.information.PersonInformation;
import com.mairuide.bill.information.PersonInformationCardTrans;
import com.mairuide.bill.information.PersonInformationService;
import com.mairuide.bill.methodsanalysis.CommonStatistics;
import com.mairuide.casemanage.service.CaseManageService;
import com.mairuide.entity.PageResult;
import com.mairuide.exception_analysis.pojo.ExceptionAnalysisDto;
import com.mairuide.inspectionReport.information.service.AlipayService;
import com.mairuide.inspectionReport.information.service.TencentQQService;
import com.mairuide.inspectionReport.information.service.TencentWechatService;
import com.mairuide.openaccountinfo.dao.OpenaccountInfoDao;
import com.mairuide.phone.PhoneRecord;

@Service
@Transactional
public class DealFlowService extends BaseServiceProxy<DealFlow> {
	@Resource
	private OpenaccountInfoDao openaccountInfoDao;

	@PersistenceContext
	private EntityManager entityManager;
	
	@Resource
	private JdbcTemplate jdbcTemplate;

	@Autowired
	private CaseManageService caseManageService;
	@Autowired
	private AlipayService alipayService;
	@Autowired
	private TencentWechatService tencentWechatService;
	@Autowired
    private TencentQQService tencentQQService;

	@Autowired
	private DealFlowRepository dealFlowRepository;
	
	@Autowired
	private PersonInformationService personInformationService;

	@Override
	protected BaseRepository<DealFlow> getBaseRepository() {
		return dealFlowRepository;
	}
	
	@Override
	protected EntityManager getEntityManager() {
		return entityManager;
	}
	
	/**
	 * 创建自定义导入临时表
	 * @param uid
	 */
	public void createTempTable(String uid,List<Integer> tempRowsNumber) {
		StringBuffer dropTableSql = new StringBuffer();
		dropTableSql.append("select count(*) AS COUNT from user_tables where table_name = 'TEMP_"+uid+"'");
		Map<String,Object> m = jdbcTemplate.queryForMap(dropTableSql.toString());
		//判断临时表是否存在
		if(null != m && Integer.parseInt(m.get("COUNT").toString()) <= 0) {
			StringBuffer sbf = new StringBuffer();
			sbf.append("create table TEMP_"+uid+" (");
			//不存在则创建临时表，表字段个数取文件前十行最长的
			int maxRowsNumber = Collections.max(tempRowsNumber) + 1;
			for(int i = 0 ; i < maxRowsNumber; i ++ ) {
				if(i == 0) {
					sbf.append("temp_"+i+" number,");
				}else {
					sbf.append("temp_"+i+" varchar(255),");
				}
			}
			sbf.deleteCharAt(sbf.length() - 1);
			sbf.append(") SEGMENT CREATION IMMEDIATE " ); 
			sbf.append("PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING ");
			sbf.append("STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 ");
			sbf.append("PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ");
			sbf.append("TABLESPACE BILL_DB");
			jdbcTemplate.execute(sbf.toString());
		}
	}
	
	/**
	 * 删除临时表
	 * @param uid
	 */
	public void deleteTempTable(String uid) {
		StringBuffer dropTableSql = new StringBuffer();
		dropTableSql.append("select count(*) AS COUNT from user_tables where table_name = 'TEMP_"+uid+"'");
		Map<String,Object> m = jdbcTemplate.queryForMap(dropTableSql.toString());
		//判断临时表是否存在
		if(null != m && Integer.parseInt(m.get("COUNT").toString()) > 0) {
			StringBuffer truncateSbfSql = new StringBuffer();
			//删除表信息
			truncateSbfSql.append("drop table TEMP_"+uid);
			jdbcTemplate.execute(truncateSbfSql.toString());
		}
	}
	
	/**
	 * 分页查询自定义导入临时表信息
	 * @param uuid
	 * @param page
	 * @param rows
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageResult findTemp(String uuid,Integer page,Integer rows){
		StringBuffer sb=new StringBuffer("SELECT *");
        sb.append(" FROM TEMP_"+uuid+" ORDER BY TEMP_0 ASC");
        long count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM ("+sb.toString()+" ) t", long.class);
        Query query = this.entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setFirstResult((page-1) * rows);
        query.setMaxResults(rows);
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(count,list,null);
        return pageResult;
    }
	
	/**
	 * 查询自定义表字段个数
	 * @param caseId
	 * @return
	 */
	public Long findTempCloumSize(String caseId) {
		String sql = "select count(*) from user_tab_columns t where t.TABLE_NAME='TEMP_"+caseId+"'";
		return jdbcTemplate.queryForObject(sql, long.class);
	}
	
	public void batchSave(List<DealFlow> list) {
		if (list==null||list.size()==0) return;
		String whichTable = caseManageService.getWhichTable(list.get(0).getCasemanageId());
		if(list!=null && list.size()>0){
			final List<DealFlow> tempList = list;
			String sql="insert into "+whichTable+"(BILL_ID, ACCOUNT_BODY, ACCOUNT_BODY_NUMBER, ACCOUNT_BODY_CARD, SIGN, "
					+ "TRADE_DATE, ACCOUNT_RIVAL, ACCOUNT_RIVAL_NUMBER, ACCOUNT_RIVAL_CARD, ACCOUNT_MONEY, "
					+ "SURPLUS_MONEY, CONTENT_ABSTRACT, ACCOUNT_BODY_BANK, ACCOUNT_RIVAL_BANK, ACCOUNT_ADDRESS, "
					+ "ACCOUNT_MODE, SERIAL_NUMBER, MAC_ADDRESS, IP_ADDRESS, CURRENCY, "
					+ "REMARKS, CASEMANAGE_ID, CREATE_ID, TRADING_ORGANIZATION_NUMBER, "
					+ "TELLER_NUMBER, INSTITUTION_NUMBER, CREATE_TIME,TEMPLATE_TAG,ID_NUMBER,"
					+ "UUID) ";
			sql+="values(SEQ_DEAL_FLOW.nextval,?,?,?,?,"
					+ "?,?,?,?,?,"
					+ "?,?,?,?,?,"
					+ "?,?,?,?,?,"
					+ "?,?,?,?,"
					+ "?,?,sysdate,?,?,"
					+ "?)";
			jdbcTemplate.batchUpdate(sql,
					new BatchPreparedStatementSetter() {
				public void setValues(PreparedStatement ps, int i)
						throws SQLException {
					ps.setString(1, tempList.get(i).getAccountBody()==null?"":tempList.get(i).getAccountBody());
					ps.setString(2, tempList.get(i).getAccountBodyNumber()==null?"":tempList.get(i).getAccountBodyNumber());
					ps.setString(3, tempList.get(i).getAccountBodyCard()==null?"":tempList.get(i).getAccountBodyCard());
					ps.setInt(4, tempList.get(i).getSign()==null?0:tempList.get(i).getSign());
					
					ps.setTimestamp(5, tempList.get(i).getTradeDate()==null?null:(new Timestamp(tempList.get(i).getTradeDate().getTime())));
					ps.setString(6, tempList.get(i).getAccountRival()==null?"":tempList.get(i).getAccountRival());
					ps.setString(7, tempList.get(i).getAccountRivalNumber()==null?"":tempList.get(i).getAccountRivalNumber());
					ps.setString(8, tempList.get(i).getAccountRivalCard()==null?"":tempList.get(i).getAccountRivalCard());
					ps.setBigDecimal(9, tempList.get(i).getAccountMoney()==null?(new BigDecimal(0)):tempList.get(i).getAccountMoney());

					ps.setBigDecimal(10, tempList.get(i).getSurplusMoney()==null?(new BigDecimal(0)):tempList.get(i).getSurplusMoney());
					ps.setString(11, tempList.get(i).getContentAbstract()==null?"":tempList.get(i).getContentAbstract());
					ps.setString(12, tempList.get(i).getAccountBodyBank()==null?"":tempList.get(i).getAccountBodyBank());
					ps.setString(13, tempList.get(i).getAccountRivalBank()==null?"":tempList.get(i).getAccountRivalBank());
					ps.setString(14, tempList.get(i).getAccountAddress()==null?"":tempList.get(i).getAccountAddress());

					ps.setString(15, tempList.get(i).getAccountMode()==null?"":tempList.get(i).getAccountMode());
					ps.setString(16, tempList.get(i).getSerialNumber()==null?"":tempList.get(i).getSerialNumber());
					ps.setString(17, tempList.get(i).getMacAddress()==null?"":tempList.get(i).getMacAddress());
					ps.setString(18, tempList.get(i).getIpAddress()==null?"":tempList.get(i).getIpAddress());
					ps.setString(19, tempList.get(i).getCurrency()==null?"":tempList.get(i).getCurrency());

					ps.setString(20, tempList.get(i).getRemarks()==null?"":tempList.get(i).getRemarks());
					ps.setString(21, tempList.get(i).getCasemanageId()==null?"":tempList.get(i).getCasemanageId());
					ps.setInt(22, tempList.get(i).getCreateId()==null?1:tempList.get(i).getCreateId());
					ps.setString(23, tempList.get(i).getTradingOrganizationNumber()==null?"":tempList.get(i).getTradingOrganizationNumber());
					
					ps.setString(24, tempList.get(i).getTellerNumber()==null?"":tempList.get(i).getTellerNumber());
					ps.setString(25, tempList.get(i).getInstitutionNumber()==null?"":tempList.get(i).getInstitutionNumber());
					ps.setString(26, tempList.get(i).getTemplateTag()==null?"":tempList.get(i).getTemplateTag());
					ps.setString(27, tempList.get(i).getIdNumber()==null?"":tempList.get(i).getIdNumber());

					ps.setString(28, tempList.get(i).getUuid()==null?"":tempList.get(i).getUuid());
				}
				public int getBatchSize() {
					return tempList.size();
				}
			}); 
		}
	}


	/**
	 * 批量更新
	 * @param
	 */
	public void batchUpdate(String caseManageId) {
		String whichTable = caseManageService.getWhichTable(caseManageId);
		String sb="UPDATE "+whichTable+" t \n" +
				"SET ( ID_NUMBER, ACCOUNT_BODY, ACCOUNT_BODY_NUMBER ) = (\n" +
				"\tSELECT\n" +
				"\t\tmax( ID_NUMBER ) ID_NUMBER,\n" +
				"\t\tmax( ACCOUNT_OWNER ) ACCOUNT_BODY,\n" +
				"\t\tmax( TRADE_ACCOUNT ) ACCOUNT_BODY_NUMBER \n" +
				"\tFROM\n" +
				"\t\tTB_OPENACCOUNT_INFO o \n" +
				"\tWHERE\n" +
				"\t\tTRADE_CARD IS NOT NULL \n" +
				"\t\tAND (o.ACCOUNT_OWNER IS NOT NULL or o.ID_NUMBER IS NOT NULL or o.TRADE_ACCOUNT IS NOT NULL ) \n" +
				"\t\tAND o.CASEMANAGE_ID = t.CASEMANAGE_ID \n" +
				"\t\tAND o.TRADE_CARD = t.account_body_card \n" +
				"\t\tAND t.CASEMANAGE_ID = '"+caseManageId+"' \n" +
				"\tGROUP BY\n" +
				"\t\ttrade_card \n" +
				"\t)"+ 
				" where account_body_card in (select distinct trade_card from TB_OPENACCOUNT_INFO where CASEMANAGE_ID = '"+caseManageId+"'  AND (ACCOUNT_OWNER IS NOT NULL or ID_NUMBER IS NOT NULL or TRADE_ACCOUNT IS NOT NULL  ) )";
		jdbcTemplate.execute(sb);//修改卡号对应的开户基本信息
		
		String sb1="UPDATE "+whichTable+" t \r\n" + 
				"SET ( ID_NUMBER, ACCOUNT_BODY, ACCOUNT_BODY_CARD ) = (\r\n" + 
				"  SELECT\r\n" + 
				"    max( ID_NUMBER ) ID_NUMBER,\r\n" + 
				"    max( ACCOUNT_OWNER ) ACCOUNT_BODY,\r\n" + 
				"    max( TRADE_CARD ) ACCOUNT_BODY_CARD \r\n" + 
				"  FROM\r\n" + 
				"    TB_OPENACCOUNT_INFO o \r\n" + 
				"  WHERE\r\n" + 
				"    o.TRADE_CARD IS NOT NULL \r\n" + 
				"    AND o.TRADE_ACCOUNT IS NOT NULL \r\n" + 
				"    AND o.CASEMANAGE_ID = t.CASEMANAGE_ID \r\n" + 
				"    AND o.TRADE_ACCOUNT = t.account_body_card \r\n" + 
				"    AND t.CASEMANAGE_ID = '"+caseManageId+"' \r\n" + 
				"  GROUP BY\r\n" + 
				"    o.TRADE_ACCOUNT \r\n" + 
				"  ) where t.account_body_card in (select distinct TRADE_ACCOUNT from TB_OPENACCOUNT_INFO where CASEMANAGE_ID = '"+caseManageId+"'  AND TRADE_ACCOUNT IS NOT NULL  AND TRADE_CARD IS NOT NULL  )";
		jdbcTemplate.execute(sb1);//修改卡号对应的开户基本信息
		
		String sb2="UPDATE "+whichTable+" t \n" +
				"SET ( account_rival ) = (\n" +
				"\tSELECT\n" +
				"\t\tmax( ACCOUNT_OWNER ) account_rival \n" +
				"\tFROM\n" +
				"\t\tTB_OPENACCOUNT_INFO o \n" +
				"\tWHERE\n" +
				"\t\tTRADE_CARD IS NOT NULL \n" +
				"\t\tAND (o.ACCOUNT_OWNER IS NOT NULL or o.ID_NUMBER IS NOT NULL or o.TRADE_ACCOUNT IS NOT NULL ) \n" +
				"\t\tAND o.CASEMANAGE_ID = t.CASEMANAGE_ID \n" +
				"\t\tAND o.TRADE_CARD = t.account_rival_card \n" +
				"\t\tAND t.CASEMANAGE_ID = '"+caseManageId+"' \n" +
				"\tGROUP BY\n" +
				"\t\ttrade_card \n" +
				"\t)"+
				" where account_rival_card in (select distinct trade_card from TB_OPENACCOUNT_INFO where CASEMANAGE_ID = '"+caseManageId+"'  AND (ACCOUNT_OWNER IS NOT NULL or ID_NUMBER IS NOT NULL or TRADE_ACCOUNT IS NOT NULL ) )";
		jdbcTemplate.execute(sb2);
		
		String sql3="UPDATE "+whichTable+" t \r\n" + 
				"SET ( ACCOUNT_RIVAL_CARD ) = (\r\n" + 
				"  SELECT\r\n" + 
				"    max( TRADE_CARD ) ACCOUNT_RIVAL_CARD \r\n" + 
				"  FROM\r\n" + 
				"    TB_OPENACCOUNT_INFO o \r\n" + 
				"  WHERE\r\n" + 
				"    o.TRADE_CARD IS NOT NULL \r\n" + 
				"    AND o.TRADE_ACCOUNT IS NOT NULL \r\n" + 
				"    AND o.CASEMANAGE_ID = t.CASEMANAGE_ID \r\n" + 
				"    AND o.TRADE_ACCOUNT = t.ACCOUNT_RIVAL_CARD \r\n" + 
				"    AND t.CASEMANAGE_ID = '"+caseManageId+"' \r\n" + 
				"  GROUP BY\r\n" + 
				"    o.TRADE_ACCOUNT \r\n" + 
				"  ) where t.ACCOUNT_RIVAL_CARD in (select distinct TRADE_ACCOUNT from TB_OPENACCOUNT_INFO where CASEMANAGE_ID = '"+caseManageId+"'  AND TRADE_ACCOUNT IS NOT NULL  AND TRADE_CARD IS NOT NULL  )";
		jdbcTemplate.execute(sql3);
	}
	
	/**
	 * 更新人员信息
	 * @param caseManageId
	 */
	public void updatePersonInformation(String caseManageId) {
		String whichTable = caseManageService.getWhichTable(caseManageId);
		StringBuffer sbf = new StringBuffer();
		sbf.append("select t.id_number AS ID_NUMBER, t.account_body AS ACCOUNT_BODY from "+whichTable+" t where t.casemanage_id = '"+caseManageId+"' ");
		sbf.append("and t.is_delete =0 and (t.id_number is not null or t.account_body is not null) group by t.id_number, t.account_body");
		List<Map<String,Object>> lsm = jdbcTemplate.queryForList(sbf.toString());
		if(null != lsm && lsm.size() > 0 ) {
			for(Map<String,Object> m : lsm) {
				String idNumber = m.get("ID_NUMBER") == null ? "" : m.get("ID_NUMBER").toString();
				String accountBody = m.get("ACCOUNT_BODY") == null ? "" : m.get("ACCOUNT_BODY").toString();
				PersonInformation pi = null;
            	if(com.mairuide._frame.utils.StringUtils.isEmpty(idNumber)) {
            		pi = personInformationService.findByPersonName(accountBody);
            	}else {
            		pi = personInformationService.findPersonInformationByIdNumber(idNumber);
            	}
				if(null == pi) {
					sbf.setLength(0);
					sbf.append("insert into tb_person_information(p_id,id_number,person_name,gender,birth_date,national,birth_place,marital_status,data_source,body_type,casemanager_id) ");
					sbf.append("values(SEQ_PERSON_INFORMATION.nextval,'"+idNumber+"','"+accountBody+"','','','','','','系统账单导入','','"+caseManageId+"')");
					/* 同步人员基础信息数据 */
					jdbcTemplate.execute(sbf.toString());
				}
			}
		}
	}

	/**
	 * @Author 刘中华
	 * @Date 2019/3/19 15:41
	 * @Param [prop, page, size]
	 * @return com.mairuide.entity.PageResult
	 * @Description  分页
	 **/
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageResult getPageDetails(Map<String,String> prop,Integer page, Integer size){
		String whichTable = caseManageService.getWhichTable(prop.get("casemanageId"));
		StringBuffer sb=new StringBuffer();
		String bodyQuery="select  * ";
		String countQuery="select  count(1) ";
		sb.append("  from "+ whichTable  +" where 1=1  ");
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("casemanageId"))){
			sb.append(" AND CASEMANAGE_ID = '"+prop.get("casemanageId")+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("accountBody"))){
			sb.append(" AND ACCOUNT_BODY = '"+prop.get("accountBody")+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("accountRival"))){
			sb.append(" AND ACCOUNT_RIVAL = '"+prop.get("accountRival")+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("accountBodyCard"))){
			sb.append(" AND ACCOUNT_BODY_CARD = '"+prop.get("accountBodyCard")+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("accountBodyNumber"))){
			sb.append(" AND ACCOUNT_BODY_NUMBER = '"+prop.get("accountBodyNumber")+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("accountRivalCard"))){
			sb.append(" AND ACCOUNT_RIVAL_CARD = '"+prop.get("accountRivalCard")+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("sign"))){
			sb.append(" AND sign = "+prop.get("sign")+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("minTradeMoney"))){
			sb.append(" AND account_money >= "+prop.get("minTradeMoney")+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("maxTradeMoney"))){
			sb.append(" AND account_money < "+prop.get("maxTradeMoney")+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("minAccountMoney"))){
			sb.append(" AND surplus_money >= "+prop.get("minAccountMoney")+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("maxAccountMoney"))){
			sb.append(" AND surplus_money <= "+prop.get("maxAccountMoney")+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("minAccountMoney"))){
			sb.append(" AND surplus_money >= "+prop.get("minAccountMoney")+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("beginTime"))){
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= "+prop.get("beginTime")+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("endTime"))){
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= "+prop.get("endTime")+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("month"))){
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm') = '"+prop.get("month")+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("content_abstract"))){
			sb.append(" AND content_abstract LIKE '%"+prop.get("content_abstract")+"%' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("prop"))&& org.apache.commons.lang3.StringUtils.isNotBlank(prop.get("order"))){
			sb.append(" ORDER BY " + prop.get("prop") + " " + prop.get("order"));
		}else{
			sb.append(" ORDER BY ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD ");
		}
		long count = jdbcTemplate.queryForObject(countQuery+sb.toString(), long.class);
		Query query = this.entityManager.createNativeQuery(bodyQuery+sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(DealFlow.class));
		// 设置分页
		query.setFirstResult((page - 1) * size);
		query.setMaxResults(size);
		// 获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult pageResult = new PageResult(count, list);
		return pageResult;
	}
	
	public PageResult getPageDetails_easyui(ExceptionAnalysisDto dto){
		String whichTable = caseManageService.getWhichTable(dto.getCaseId());
		StringBuffer sb=new StringBuffer();
		String bodyQuery="select  * ";
		String countQuery="select  count(1) ";
		sb.append("  from "+ whichTable  +" where 1=1  ");
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getCaseId())){
			sb.append(" AND CASEMANAGE_ID = '"+dto.getCaseId()+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getAccountBody())){
			sb.append(" AND ACCOUNT_BODY = '"+dto.getAccountBody()+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getAccountRival())){
			sb.append(" AND ACCOUNT_RIVAL = '"+dto.getAccountRival()+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getAccountBodyCard())){
			sb.append(" AND ACCOUNT_BODY_CARD = '"+dto.getAccountBodyCard()+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getAccountBodyNumber())){
			sb.append(" AND ACCOUNT_BODY_NUMBER = '"+dto.getAccountBodyNumber()+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getAccountRivalCard())){
			sb.append(" AND ACCOUNT_RIVAL_CARD = '"+dto.getAccountRivalCard()+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getSign())){
			sb.append(" AND sign = "+dto.getSign()+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getMinTradeMoney())){
			sb.append(" AND account_money >= "+dto.getMinTradeMoney()+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getMaxTradeMoney())){
			sb.append(" AND account_money < "+dto.getMaxTradeMoney()+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getMinAccountMoney())){
			sb.append(" AND surplus_money >= "+dto.getMinAccountMoney()+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getMaxAccountMoney())){
			sb.append(" AND surplus_money < "+dto.getMaxAccountMoney()+" ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getBeginTime())){
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '"+dto.getBeginTime()+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getEndTime())){
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '"+dto.getEndTime()+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getMonth())){
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm') = '"+dto.getMonth()+"' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getContent_abstract())){
			sb.append(" AND content_abstract LIKE '%"+dto.getContent_abstract()+"%' ");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(dto.getSort())&& org.apache.commons.lang3.StringUtils.isNotBlank(dto.getOrder())){
			sb.append(" ORDER BY " + dto.getSort() + " " + dto.getOrder());
		}else{
			sb.append(" ORDER BY ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD ");
		}
		long count = jdbcTemplate.queryForObject(countQuery+sb.toString(), long.class);
		Query query = this.entityManager.createNativeQuery(bodyQuery+sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(DealFlow.class));
		// 设置分页
		query.setFirstResult((dto.getPage() - 1) * dto.getRows());
		query.setMaxResults(dto.getRows());
		// 获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult pageResult = new PageResult(count, list);
		return pageResult;
	}
	
	/**
	 * 分页查询账单信息
	 * @param vo
	 * @param page
	 * @param sortName
	 * @param sortOrder
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Object findBillByPage(DealFlowVo vo) {
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select a.*,b.SURPLUS_MONEY as ACCOUNTBALANCE from (SELECT sum(abs(ACCOUNT_MONEY)) as accountMoney,count(*) as createId,min(TRADE_DATE ) as tradeDate,max(TRADE_DATE ) as createTime,MAX(ACCOUNT_RIVAL) as accountRival,ACCOUNT_BODY_CARD as accountBodyCard,COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS ACCOUNTINFLOW," +
				"COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS ACCOUNTOUT," +
				"COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 )-COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS ACCOUNTINFLOWALL,ACCOUNT_BODY as accountBody,df.account_rival_card AS accountRivalCard");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" FROM "+whichTable+" df where IS_DELETE=0 ");

		if(StringUtils.isNotEmpty(vo.getCasemanageId())) {
			fromWhereSql.append("AND casemanage_id = '"+vo.getCasemanageId() +"' ");
		}
		
		if(StringUtils.isNotEmpty(vo.getAccountBody())) {
			fromWhereSql.append("AND account_body like '%"+vo.getAccountBody()+"%' ");
		}
		if(StringUtils.isNotEmpty(vo.getAccountBodyNumber())) {
			fromWhereSql.append("AND account_body_number like '%"+vo.getAccountBodyNumber()+"%' ");
		}

		if(StringUtils.isNotEmpty(vo.getAccountBodyCard())) {
			fromWhereSql.append("AND account_body_card like '%"+vo.getAccountBodyCard()+"%' ");
		}
		
		if(StringUtils.isNotEmpty(vo.getAccountRival())) {
			fromWhereSql.append("AND account_rival like '%"+vo.getAccountRival()+"%' ");
		}
		if(StringUtils.isNotEmpty(vo.getAccountRivalNumber())) {
			fromWhereSql.append("AND account_rival_number like '%"+vo.getAccountRivalNumber()+"%' ");
		}
		
		if(StringUtils.isNotEmpty(vo.getAccountRivalCard())) {
			fromWhereSql.append("AND account_rival_card like '%"+vo.getAccountRivalCard()+"%' ");
		}
		
		if(StringUtils.isNotEmpty(vo.getAccountAddress())) {
			fromWhereSql.append("AND account_address like '%"+vo.getAccountAddress()+"%' ");
		}
		
		if(StringUtils.isNotEmpty(vo.getRemarks())) {
			fromWhereSql.append("AND remarks like '%"+vo.getRemarks()+"%' ");
		}
		
		if(vo.getSign()!=null && vo.getSign()!=-1) {
			fromWhereSql.append("AND sign= '"+vo.getSign() +"' ");
		}
		
		fromWhereSql.append(" and (1=0 ");
		if(StringUtils.isNotEmpty(vo.getCheckedAccount())) {
			if (vo.getCheckedAccount().split(",").length<1000){
				String cards = "";
				for(String str:vo.getCheckedAccount().split(",")) {
					if(StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if(StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards+"'"+accountBodyCard+"',";
						}
					}
				}
				if(cards.endsWith(",")) {
					cards = cards.substring(0,cards.length()-1);
				}
				if(cards.length()>0) {
					fromWhereSql.append(" or account_body_card in ("+cards+")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(") ");
		
		if(vo.getIsCash()==1) {
			//现金交易分析
			fromWhereSql.append("  and (content_abstract like '%存%' or content_abstract like '%现%') ");
		}
		
		if(StringUtils.isNotEmpty(vo.getDetailKeyword())) {
			//对交易类型 摘要 备注 MAC地址 IP地址 查询
			fromWhereSql.append(" and (content_abstract like '%"+vo.getDetailKeyword()+"%' or remarks like '%"+vo.getDetailKeyword()+"%' or mac_address like '%"+vo.getDetailKeyword()+"%' or ip_address like '%"+vo.getDetailKeyword()+"%' "
					+ " or account_body like '%"+vo.getDetailKeyword()+"%' or account_body_card like '%"+vo.getDetailKeyword()+"%' "
							+ "or account_rival like '%"+vo.getDetailKeyword()+"%' or account_rival_card like '%"+vo.getDetailKeyword()+"%') ");
		}
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(vo.getStarTime())) {
			fromWhereSql.append(" AND TRADE_DATE >= to_date('"+vo.getStarTime()+"','yyyy-mm-dd') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(vo.getEndTime())) {
			fromWhereSql.append(" AND TRADE_DATE <= to_date('"+vo.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		//归属银行
		fromWhereSql.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD ,account_rival_card having 1=1");
        if(StringUtils.isNotEmpty(vo.getMinMoney())) {
            fromWhereSql.append(" and sum(abs( ACCOUNT_MONEY ))>='"+vo.getMinMoney()+"' ");
        }
        if(StringUtils.isNotEmpty(vo.getMaxMoney())) {
            fromWhereSql.append(" and sum(abs( ACCOUNT_MONEY ))<='"+vo.getMaxMoney()+"' ");
        }
        fromWhereSql.append(") a left join (select ACCOUNT_BODY_CARD,TRADE_DATE,max(SURPLUS_MONEY) SURPLUS_MONEY from "+whichTable
				+" group by ACCOUNT_BODY_CARD,TRADE_DATE) b on a.createTime=b.trade_date and a.accountBodyCard=b.ACCOUNT_BODY_CARD");
		if (org.apache.commons.lang3.StringUtils.isNotBlank(vo.getSort())){
			fromWhereSql.append(" order by "+vo.getSort()+" "+vo.getOrder());
		}else {
			fromWhereSql.append(" ORDER BY accountMoney DESC ");
		}
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a ",long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		//将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		//设置分页
		query.setFirstResult((vo.getPage() - 1) * vo.getRows());
		query.setMaxResults(vo.getRows());
		//获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(count,list);
		return pageResult;
	}
	
	/**
	 * 查询出来账单详情里面的D3人员关系图
	 * @param vo
	 * @return
	 */
	public Map<String, Object> detailD3(DealFlowVo dealFlowdto){
		String whichTable = caseManageService.getWhichTable(dealFlowdto.getCasemanageId());
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT (case when df.ACCOUNT_BODY is not null then df.ACCOUNT_BODY else df.ACCOUNT_BODY_card end) account_body, ");
        sb.append(" (case when df.ACCOUNT_RIVAL is not null then df.ACCOUNT_RIVAL else df.ACCOUNT_RIVAL_card end) ACCOUNT_RIVAL, ");
        sb.append(" sum(df.inMoney) as inMoney,sum(df.inNumber) as inNumber,sum(df.outMoney) as outMoney,sum(df.outNumber) as outNumber ");
        sb.append("  FROM (");
        sb.append(" SELECT max(ACCOUNT_BODY) AS ACCOUNT_BODY,ACCOUNT_BODY_CARD,max(BODY_TYPE) AS BODY_TYPE,MAX(ACCOUNT_RIVAL) AS ACCOUNT_RIVAL,account_rival_card,SUM( abs(account_money) ) AS totalMoney,COUNT( account_rival_card ) AS totalNumber,");
        sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
        sb.append(" COUNT( CASE WHEN sign = '0' THEN account_money END ) AS inNumber,");
        sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
        sb.append(" COUNT( CASE WHEN sign = '1' THEN account_money END ) AS outNumber ");
        sb.append(" FROM "+whichTable+" WHERE casemanage_id = '"+dealFlowdto.getCasemanageId()+"'");
        
        sb.append(" and (1=0 ");
        if(StringUtils.isNotEmpty(dealFlowdto.getCheckedAccount())){
        	if(!"-1".equals(dealFlowdto.getCheckedAccount())) {
        		if (dealFlowdto.getCheckedAccount().split(",").length<1000){
        			String cards = "";
        			for(String str:dealFlowdto.getCheckedAccount().split(",")) {
        				if(StringUtils.isNotEmpty(str)) {
        					String accountBodyCard = str.split("_")[1];
        					if(StringUtils.isNotEmpty(accountBodyCard)) {
        						cards = cards+"'"+accountBodyCard+"',";
        					}
        				}
        			}
        			if(cards.endsWith(",")) {
        				cards = cards.substring(0,cards.length()-1);
        			}
        			if(cards.length()>0) {
        				sb.append(" or account_body_card in ("+cards+")");
        			}
        		}
			}
        }
        sb.append(") ");
        
        if(StringUtils.isNotEmpty(dealFlowdto.getMinMoney())){
            sb.append(" AND account_money >="+dealFlowdto.getMinMoney());
        }
        if(dealFlowdto.getSign()!=null && dealFlowdto.getSign()!=-1) {
			sb.append("AND sign= '"+dealFlowdto.getSign() +"' ");
		}
        if(StringUtils.isNotEmpty(dealFlowdto.getMaxMoney())){
            sb.append(" AND account_money <="+dealFlowdto.getMaxMoney());
        }
        if(StringUtils.isNotEmpty(dealFlowdto.getDetailKeyword())) {
			//对交易类型 摘要 备注 MAC地址 IP地址 查询
			sb.append(" and (content_abstract like '%"+dealFlowdto.getDetailKeyword()+"%' or remarks like '%"+dealFlowdto.getDetailKeyword()+"%' or mac_address like '%"+dealFlowdto.getDetailKeyword()+"%' or ip_address like '%"+dealFlowdto.getDetailKeyword()+"%') ");
		}
        if (StringUtils.isNotEmpty(dealFlowdto.getAccountRival())){
            sb.append(" AND account_rival like '%"+dealFlowdto.getAccountRival()+"%'");
        }
        if (StringUtils.isNotEmpty(dealFlowdto.getStarTime())){
            sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '"+dealFlowdto.getStarTime()+"'");
        }
        if (StringUtils.isNotEmpty(dealFlowdto.getEndTime())){
            sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') <= '"+dealFlowdto.getEndTime()+" 23:59:59'");
        }
        sb.append(" GROUP BY ACCOUNT_BODY_CARD,account_rival_card ) df where  df.TOTALNUMBER>0 group by (case when df.ACCOUNT_BODY is not null then df.ACCOUNT_BODY else df.ACCOUNT_BODY_card end),(case when df.ACCOUNT_RIVAL is not null then df.ACCOUNT_RIVAL else df.ACCOUNT_RIVAL_card end) ");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Map<String, Object> hashMap = new HashMap<>();
        if (maps.size()>0){
            List<String> selectedCards = Arrays.asList(dealFlowdto.getCheckedAccount().split(","));//所有已选单位的合集 -1代表全选
            
            List<Map<String, Object>> nodes = new ArrayList<Map<String, Object>>();
            List<Map<String, Object>> links = new ArrayList<Map<String, Object>>();
            List<String> nodeNameList = new ArrayList<String>();
            for (int i=0;i<maps.size();i++){
                /** 封装交易主题D3 **/
                encapsulationAccountBodyD3(dealFlowdto, selectedCards, nodes, nodeNameList, maps.get(i));
            }

            for (int i=0;i<maps.size();i++){
            	/** 封装交易对手D3 **/
                encapsulationAccountRivalD3(maps, nodes, nodeNameList, i);
            }
            /** 封装交易主体交易对手line start**/
            for (int i=0;i<maps.size();i++){
                Map<String, Object> map = maps.get(i);
                String nodeName = map.get("ACCOUNT_BODY")==null?(map.get("ACCOUNT_BODY_CARD").toString()):(map.get("ACCOUNT_BODY").toString());
                String nodeName2 = map.get("ACCOUNT_RIVAL")==null?(map.get("ACCOUNT_RIVAL_CARD").toString()):(map.get("ACCOUNT_RIVAL").toString());
                if(map.get("INNUMBER")!=null && Integer.valueOf(map.get("INNUMBER").toString())>0) {
                	HashMap<String, Object> link = new HashMap<>();
                	for (Map<String, Object> m:nodes){
                		if (nodeName2.equals((String)m.get("name"))){
                			link.put("node1",m.get("id"));
                		}
                		if (nodeName.equals((String)m.get("name"))){
                			link.put("node2",m.get("id"));
                		}
                		link.put("name","交易"+map.get("INNUMBER")+"次,金额"+map.get("INMONEY")+"元");
                	}
                	if(!link.keySet().contains("node1")||!link.keySet().contains("node2")) continue;
                	links.add(link);
                }
                if(map.get("OUTNUMBER")!=null && Integer.valueOf(map.get("OUTNUMBER").toString())>0) {
	                HashMap<String, Object> link2 = new HashMap<>();
	                for (Map<String, Object> m:nodes){
	                	if (nodeName2.equals((String)m.get("name"))){
	                		link2.put("node2",m.get("id"));
	                	}
	                	if (nodeName.equals((String)m.get("name"))){
	                		link2.put("node1",m.get("id"));
	                	}
	                	link2.put("name","交易"+map.get("OUTNUMBER")+"次,金额"+map.get("OUTMONEY")+"元");
	                }
	                if(!link2.keySet().contains("node1")||!link2.keySet().contains("node2")) continue;
	                links.add(link2);
                }
            }
            /** 封装交易主体交易对手line end**/
            
            hashMap.put("links",links);
            hashMap.put("nodes",nodes);
        }

		return hashMap;
	}
	
	public PageResult detailD3DialogData(DealFlowVo dealflowVO){
        String whichTable = caseManageService.getWhichTable(dealflowVO.getCasemanageId());
        StringBuffer sb = new StringBuffer();
        String bodyQuery="select * ";
		String countQuery="select count(1) ";
        sb.append(" from "+whichTable+" where is_delete = '0' and casemanage_id = '"+dealflowVO.getCasemanageId()+"'");
        if(StringUtils.isNotEmpty(dealflowVO.getName1()) && StringUtils.isNotEmpty(dealflowVO.getName2())) {
        	if(dealflowVO.getCheckedAccount().indexOf(dealflowVO.getName1())>=0) {
        		//说明是查的交易主体流出金额，可以直接查询
        		sb.append(" and sign=1 and (account_rival='"+dealflowVO.getName2()+"' or account_rival_card='"+dealflowVO.getName2()+"') and (account_body='"+dealflowVO.getName1()+"' or account_body_card='"+dealflowVO.getName1()+"')");
        	}else {
        		sb.append(" and sign=0 and (account_rival='"+dealflowVO.getName1()+"' or account_rival_card='"+dealflowVO.getName1()+"') and (account_body='"+dealflowVO.getName2()+"' or account_body_card='"+dealflowVO.getName2()+"')");
        	}
        }else {
	        if (dealflowVO.getSign()!=null && dealflowVO.getSign()!=-1){
	            sb.append(" and sign = "+dealflowVO.getSign());
	        }
        }
        if(StringUtils.isNotEmpty(dealflowVO.getMinMoney())){
            sb.append(" AND account_money >="+dealflowVO.getMinMoney());
        }
        if(StringUtils.isNotEmpty(dealflowVO.getMaxMoney())){
            sb.append(" AND account_money <="+dealflowVO.getMaxMoney());
        }
        /* 交易开始日期 */
        if(StringUtils.isNotEmpty(dealflowVO.getStarTime())) {
            sb.append(" AND TRADE_DATE >= to_date('"+dealflowVO.getStarTime()+"','yyyy-mm-dd') ");
        }

        /* 交易结束日期 */
        if(StringUtils.isNotEmpty(dealflowVO.getEndTime())) {
            sb.append(" AND TRADE_DATE <= to_date('"+dealflowVO.getEndTime()+" 23:59:59','yyyy-mm-dd HH24:MI:SS') ");
        }
        if(StringUtils.isNotEmpty(dealflowVO.getDetailKeyword())) {
			//对交易类型 摘要 备注 MAC地址 IP地址 查询
			sb.append(" and (content_abstract like '%"+dealflowVO.getDetailKeyword()+"%' or remarks like '%"+dealflowVO.getDetailKeyword()+"%' or mac_address like '%"+dealflowVO.getDetailKeyword()+"%' or ip_address like '%"+dealflowVO.getDetailKeyword()+"%') ");
		}

        if (StringUtils.isNotEmpty(dealflowVO.getSort())){
            sb.append(" order by "+dealflowVO.getSort()+" "+dealflowVO.getOrder());
        }else {
        	sb.append(" order by trade_date asc ");
        }

        long count = jdbcTemplate.queryForObject(countQuery+sb.toString(),long.class);
		Query query = this.entityManager.createNativeQuery(bodyQuery+sb.toString());
		//将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		//设置分页
		if(null != dealflowVO.getPage() && null != dealflowVO.getRows()) {
			query.setFirstResult((dealflowVO.getPage() - 1) * dealflowVO.getRows());
			query.setMaxResults(dealflowVO.getRows());
		}
		//获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(count,list);
		return pageResult;
    }
	
	/**
	 * 分页查询账单信息
	 * @param vo
	 * @param page
	 * @param sortName
	 * @param sortOrder
	 * @return
	 */
	public Page<DealFlow> findBillByPage(DealFlowVo vo, Pageable page,String sortName,String sortOrder) {
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT sum(abs(ACCOUNT_MONEY)) as accountMoney,count(*) as createId,min(TRADE_DATE ) as tradeDate,max(TRADE_DATE ) as createTime,MAX(ACCOUNT_RIVAL) as accountRival,ACCOUNT_BODY_CARD as accountBodyCard,ACCOUNT_BODY as accountBody,df.account_rival_card AS accountRivalCard");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" FROM "+whichTable+" df where IS_DELETE=0 ");

		if(StringUtils.isNotEmpty(vo.getCasemanageId())) {
			fromWhereSql.append("AND casemanage_id =? ");
			params.add(vo.getCasemanageId());
		}
		
		if(StringUtils.isNotEmpty(vo.getAccountBody())) {
			fromWhereSql.append("AND account_body like '%"+vo.getAccountBody()+"%' ");
		}
		if(StringUtils.isNotEmpty(vo.getAccountBodyNumber())) {
			fromWhereSql.append("AND account_body_number like '%"+vo.getAccountBodyNumber()+"%' ");
		}

		if(StringUtils.isNotEmpty(vo.getAccountBodyCard())) {
			fromWhereSql.append("AND account_body_card like '%"+vo.getAccountBodyCard()+"%' ");
		}
		
		if(StringUtils.isNotEmpty(vo.getAccountRival())) {
			fromWhereSql.append("AND account_rival like '%"+vo.getAccountRival()+"%' ");
		}
		if(StringUtils.isNotEmpty(vo.getAccountRivalNumber())) {
			fromWhereSql.append("AND account_rival_number like '%"+vo.getAccountRivalNumber()+"%' ");
		}
		
		if(StringUtils.isNotEmpty(vo.getAccountRivalCard())) {
			fromWhereSql.append("AND account_rival_card like '%"+vo.getAccountRivalCard()+"%' ");
		}
		
		if(StringUtils.isNotEmpty(vo.getAccountAddress())) {
			fromWhereSql.append("AND account_address like '%"+vo.getAccountAddress()+"%' ");
		}
		
		if(StringUtils.isNotEmpty(vo.getRemarks())) {
			fromWhereSql.append("AND remarks like '%"+vo.getRemarks()+"%' ");
		}
		
		if(vo.getSign()!=null && vo.getSign()!=-1) {
			fromWhereSql.append("AND sign=? ");
			params.add(vo.getSign());
		}
		
		fromWhereSql.append(" and (1=0 ");
		if(StringUtils.isNotEmpty(vo.getCheckedAccount())) {
			if (vo.getCheckedAccount().split(",").length<1000){
				String cards = "";
				for(String str:vo.getCheckedAccount().split(",")) {
					if(StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if(StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards+"'"+accountBodyCard+"',";
						}
					}
				}
				if(cards.endsWith(",")) {
					cards = cards.substring(0,cards.length()-1);
				}
				if(cards.length()>0) {
					fromWhereSql.append(" or account_body_card in ("+cards+")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(") ");
		
		if(vo.getIsCash()==1) {
			//现金交易分析
			fromWhereSql.append("  and (content_abstract like '%存%' or content_abstract like '%现%') ");
		}
		
		if(StringUtils.isNotEmpty(vo.getDetailKeyword())) {
			//对交易类型 摘要 备注 MAC地址 IP地址 查询
			fromWhereSql.append(" and (content_abstract like '%"+vo.getDetailKeyword()+"%' or remarks like '%"+vo.getDetailKeyword()+"%' or mac_address like '%"+vo.getDetailKeyword()+"%' or ip_address like '%"+vo.getDetailKeyword()+"%') ");
		}
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(vo.getStarTime())) {
			fromWhereSql.append(" AND TRADE_DATE >= to_date('"+vo.getStarTime()+"','yyyy-mm-dd') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(vo.getEndTime())) {
			fromWhereSql.append(" AND TRADE_DATE <= to_date('"+vo.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		//归属银行
		fromWhereSql.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD ,account_rival_card having 1=1");
        if(StringUtils.isNotEmpty(vo.getMinMoney())) {
            fromWhereSql.append(" and sum(abs( ACCOUNT_MONEY ))>='"+vo.getMinMoney()+"' ");
        }
        if(StringUtils.isNotEmpty(vo.getMaxMoney())) {
            fromWhereSql.append(" and sum(abs( ACCOUNT_MONEY ))<='"+vo.getMaxMoney()+"' ");
        }
		String orderBySql = null;
		if (org.apache.commons.lang3.StringUtils.isNotBlank(sortName)){
			orderBySql =" order by "+sortName+" "+sortOrder;
		}else {
			orderBySql = " ORDER BY accountMoney DESC ";
		}

		//封装返回数据
		return super.findPageBySql4GroupBy(DealFlow.class,selectSql.toString(), fromWhereSql.toString(), orderBySql, params, page);
	}


	/**
	 * 二级账号查询
	 * @param vo
	 * @return
	 */
	public List<DealFlow> secondBill(List<String> list,DealFlowVo vo){
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		List<Object> params = new LinkedList<Object>();
		String toString = "";
		if (list.size()>0) {
			StringBuffer rivalCard = new StringBuffer();
			for (String df : list) {
				rivalCard.append("or account_body_card = '" + df + "' ");
			}
			toString = rivalCard.toString().substring(2);
		}
		String sqlString =  "SELECT ACCOUNT_BODY_CARD as accountBodyCard, sum(abs(ACCOUNT_MONEY)) as accountMoney,count(*) as createId,min(TRADE_DATE ) as tradeDate,MAX(ACCOUNT_RIVAL) as accountRival,max(TRADE_DATE ) as createTime,ACCOUNT_BODY as accountBody,account_rival_card AS accountRivalCard ";
		StringBuffer fromSqlString = new StringBuffer();
		fromSqlString.append("FROM	"+whichTable+" WHERE IS_DELETE = 0 \n" +
				"	AND CASEMANAGE_ID = '"+vo.getCasemanageId()+"' \n"+
				"	AND ACCOUNT_RIVAL_CARD IS NOT NULL \n");
		if (StringUtils.isNotEmpty(toString)) fromSqlString.append(" AND ("+toString+")");
		if(vo.getSign()!=null && vo.getSign()!=-1) {
			fromSqlString.append("AND sign= ? ");
			params.add(vo.getSign());
		}
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(vo.getStarTime())) {
			fromSqlString.append(" AND TRADE_DATE >= to_date('"+vo.getStarTime()+"','yyyy-mm-dd') ");
		}
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(vo.getEndTime())) {
			fromSqlString.append(" AND TRADE_DATE <= to_date('"+vo.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		fromSqlString.append(" GROUP BY ACCOUNT_BODY , ACCOUNT_BODY_CARD ,account_rival_card HAVING 1=1\n");
		String toRivalString = "";
		if (list.size()>0) {
			StringBuffer rivalCard = new StringBuffer();
			for (String df : list) {
				rivalCard.append("or ACCOUNT_RIVAL_CARD = '" + df + "' ");
			}
			toRivalString = rivalCard.toString().substring(2);
		}
		if (StringUtils.isNotEmpty(toString)) fromSqlString.append(" AND(NOT ("+toRivalString+"))");
		if (StringUtils.isNotEmpty(vo.getMinMoney())) {
			fromSqlString.append(" and sum(abs( ACCOUNT_MONEY ))>='"+vo.getMinMoney()+"' ");
		}
		String order = " ORDER BY accountMoney DESC ";

		return super.findListBySql(DealFlow.class,sqlString, fromSqlString.toString(), order, params);
	}

	/**
	 * 三级账号查询
	 * @param vo
	 * @return
	 */
	public List<DealFlow> thirdBill(List<String> list,DealFlowVo vo){
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		List<Object> params = new LinkedList<Object>();
		String toString = "";
		if (list.size()>0) {
			StringBuffer rivalCard = new StringBuffer();
			for (String df : list) {
				rivalCard.append("or account_body_card = '" + df + "' ");
			}
			toString = rivalCard.toString().substring(2);
		}
		String sqlString =  "SELECT ACCOUNT_BODY_CARD as accountBodyCard, sum(abs(ACCOUNT_MONEY)) as accountMoney,count(*) as createId,min(TRADE_DATE ) as tradeDate,max(TRADE_DATE ) as createTime,MAX(ACCOUNT_RIVAL) as accountRival,ACCOUNT_BODY as accountBody,account_rival_card AS accountRivalCard ";
		StringBuffer fromSqlString = new StringBuffer();
		fromSqlString.append("FROM	"+whichTable+" WHERE IS_DELETE = 0 \n" +
				"	AND CASEMANAGE_ID = '"+vo.getCasemanageId()+"' \n"+
				"	AND ACCOUNT_RIVAL_CARD IS NOT NULL \n");
		if (StringUtils.isNotEmpty(toString)) {
			fromSqlString.append(" AND ("+toString+")");
		}else {
			fromSqlString.append(" AND 1=0 ");
		}
		if(vo.getSign()!=null && vo.getSign()!=-1) {
			fromSqlString.append("AND sign= ? ");
			params.add(vo.getSign());
		}
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(vo.getStarTime())) {
			fromSqlString.append(" AND TRADE_DATE >= to_date('"+vo.getStarTime()+"','yyyy-mm-dd') ");
		}
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(vo.getEndTime())) {
			fromSqlString.append(" AND TRADE_DATE <= to_date('"+vo.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		fromSqlString.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD ,account_rival_card HAVING  1=1" );
		String toRivalString = "";
		if (list.size()>0) {
			StringBuffer rivalCard = new StringBuffer();
			for (String df : list) {
				rivalCard.append("or ACCOUNT_RIVAL_CARD = '" + df+ "' ");
			}
			toRivalString = rivalCard.toString().substring(2);
		}
		if (StringUtils.isNotEmpty(toString)) fromSqlString.append(" AND(NOT ("+toRivalString+"))");
		if(StringUtils.isNotEmpty(vo.getMinMoney())) {
			fromSqlString.append(" and sum(abs( ACCOUNT_MONEY ))>='"+vo.getMinMoney()+"' ");
		}
		String order = " ORDER BY accountMoney DESC ";

		return super.findListBySql(DealFlow.class,sqlString, fromSqlString.toString(), order, params);
	}

	/**
	 * 分页查询账单信息
	 * @param page
	 * @return
	 */
	public Page<DealFlow> findBillByPage2(DealFlowVo vo, Pageable page,String sortName,String sortOrder) {
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT df.bill_id AS billId, df.account_body AS accountBody, df.account_body_number AS accountBodyNumber ,df.account_body_card AS accountBodyCard,df.sign AS sign,df.trade_date AS tradeDate,df.account_rival AS accountRival,df.account_rival_number AS accountRivalNumber,df.account_rival_card AS accountRivalCard,df.account_money AS accountMoney,df.surplus_money AS surplusMoney,df.content_abstract AS contentAbstract,df.account_body_bank AS accountBodyBank,df.account_rival_bank AS accountRivalBank,df.account_address AS accountAddress,df.account_mode AS accountMode,df.serial_number AS serialNumber,df.mac_address AS macAddress,df.ip_address AS ipAddress,df.currency AS currency,df.remarks AS remarks");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" FROM "+whichTable+" df where IS_DELETE=0 ");
		
		if(StringUtils.isNotEmpty(vo.getCasemanageId())) {
			fromWhereSql.append("AND casemanage_id =? ");
			params.add(vo.getCasemanageId());
		}
		
		if(StringUtils.isNotEmpty(vo.getAccountBodyCard())) {
			fromWhereSql.append("AND account_body_card = '"+vo.getAccountBodyCard()+"' ");
		}
		fromWhereSql.append("  and (content_abstract like '%存%' or content_abstract like '%现%') ");

		String orderBySql = null;
		if (org.apache.commons.lang3.StringUtils.isNotBlank(sortName)){
			orderBySql =" order by "+sortName+" "+sortOrder;
		}else {
			orderBySql = " ORDER BY df.bill_id DESC";
		}
		
		//封装返回数据
		return super.findPageBySql(DealFlow.class,selectSql.toString(), fromWhereSql.toString(), orderBySql, params, page);
	}
	
	/**
	 * 账单概况
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<BillStatusDomain> findBillStatus(DealFlowVo vo,String sortName,String sortOrder) {
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		StringBuffer sb = new StringBuffer();
		sb.append("select m.*,n.surplus_money as surPlusMoney,(m.inMoney-m.outMoney) as totalInMoney from (");
		sb.append("select t.account_body as accountBody,");
		sb.append("       t.account_body_number as accountBodyNumber,");
		sb.append("       t.account_body_card as accountBodyCard,");
		sb.append("       t.account_body_bank as accountBodyBank,");
		sb.append("       count(t.bill_Id) as totalCount,");
		sb.append("       sum(t.account_money) as totalMoney,");
		sb.append("       min(t.account_address) as accountAddress,");
		sb.append("       sum(case when t.sign=0 then abs(t.account_money) else 0 end) as inMoney,");
		sb.append("       sum(case when t.sign=1 then abs(t.account_money) else 0 end) as outMoney,");
		sb.append("       min(t.trade_date) as beginDate,");
		sb.append("       max(t.trade_date) as endDate");
		sb.append("  from "+whichTable+" t");
		sb.append(" where t.is_delete = 0");
		sb.append("   and t.casemanage_id = '"+vo.getCasemanageId()+"'");
		if(StringUtils.isNotEmpty(vo.getAccountBody())) {//交易主体
			sb.append("   and t.account_body like '%"+vo.getAccountBody()+"%'");
		}
		if(StringUtils.isNotEmpty(vo.getAccountBodyNumber())) {//交易主体账号
			sb.append("   and t.account_body_number like '%"+vo.getAccountBodyNumber()+"%'");
		}
		if(StringUtils.isNotEmpty(vo.getAccountBodyCard())) {//交易主体卡号
			sb.append("   and t.account_body_card like '%"+vo.getAccountBodyCard()+"%'");
		}
		if(StringUtils.isNotEmpty(vo.getAccountAddress())) {//交易地点
			sb.append("   and t.account_address like '%"+vo.getAccountAddress()+"%'");
		}
		if(StringUtils.isNotEmpty(vo.getAccountBodyBank())) {//归属银行
			sb.append("   and t.account_body_bank like '%"+vo.getAccountBodyBank()+"%'");
		}
		if(StringUtils.isNotEmpty(vo.getRemarks())) {//归属银行
			sb.append("   and t.remarks like '%"+vo.getRemarks()+"%'");
		}
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(vo.getStarTime())) {
			sb.append(" AND t.TRADE_DATE >= to_date('"+vo.getStarTime()+"','yyyy-mm-dd') ");
		}
		if(StringUtils.isNotEmpty(vo.getMinMoney())) {
			sb.append(" and t.account_money>='"+vo.getMinMoney()+"' ");
		}
		if(StringUtils.isNotEmpty(vo.getMinMoney())) {
			sb.append(" and t.account_money<='"+vo.getMaxMoney()+"' ");
		}
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(vo.getEndTime())) {
			sb.append(" AND t.TRADE_DATE <= to_date('"+vo.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		sb.append(" group by t.account_body, t.account_body_number, t.account_body_card,t.account_body_bank ");
		sb.append("  ) m");
		sb.append(" left join  ");
		sb.append(" (select max(surplus_money) as surplus_money,ACCOUNT_BODY_CARD,trade_date from "+whichTable+" where casemanage_id = '"+vo.getCasemanageId()+"' group by ACCOUNT_BODY_CARD,TRADE_DATE ) n");
		sb.append(" on m.accountBodyCard = n.account_body_card and m.endDate=n.trade_date");
		if (org.apache.commons.lang3.StringUtils.isNotBlank(sortName)){
			sb.append(" order by "+sortName+" "+sortOrder);
		}
		Query query = getEntityManager().createNativeQuery(sb.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(BillStatusDomain.class));
		//封装返回数据
		return query.getResultList();
	}
	
	/**
	 * 账单概况
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageResult findBillStatus4Page(DealFlowVo vo,String sortName,String sortOrder,Integer page,Integer size) {
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		StringBuffer sb = new StringBuffer();
		String bodyQuery="select  m.*,n.surplus_money as surPlusMoney,(m.inMoney-m.outMoney) as totalInMoney ";
		String countQuery="select  count(1) ";
		sb.append(" from (");
		sb.append("select t.account_body as accountBody,");
		sb.append("       t.account_body_number as accountBodyNumber,");
		sb.append("       t.account_body_card as accountBodyCard,");
		sb.append("       t.account_body_bank as accountBodyBank,");
		sb.append("       count(t.bill_Id) as totalCount,");
		sb.append("       sum(t.account_money) as totalMoney,");
		sb.append("       min(t.account_address) as accountAddress,");
		sb.append("       sum(case when t.sign=0 then abs(t.account_money) else 0 end) as inMoney,");
		sb.append("       sum(case when t.sign=1 then abs(t.account_money) else 0 end) as outMoney,");
		sb.append("       min(t.trade_date) as beginDate,");
		sb.append("       max(t.trade_date) as endDate");
		sb.append("  from "+whichTable+" t");
		sb.append(" where t.is_delete = 0 and t.account_body_card is not null");
		sb.append("   and t.casemanage_id = '"+vo.getCasemanageId()+"'");
		if(StringUtils.isNotEmpty(vo.getAccountBody())) {//交易主体
			sb.append("   and t.account_body like '%"+vo.getAccountBody()+"%'");
		}
		if(StringUtils.isNotEmpty(vo.getAccountBodyNumber())) {//交易主体账号
			sb.append("   and t.account_body_number like '%"+vo.getAccountBodyNumber()+"%'");
		}
		if(StringUtils.isNotEmpty(vo.getAccountBodyCard())) {//交易主体卡号
			sb.append("   and t.account_body_card like '%"+vo.getAccountBodyCard()+"%'");
		}
		if(StringUtils.isNotEmpty(vo.getAccountAddress())) {//交易地点
			sb.append("   and t.account_address like '%"+vo.getAccountAddress()+"%'");
		}
		if(StringUtils.isNotEmpty(vo.getAccountBodyBank())) {//归属银行
			sb.append("   and t.account_body_bank like '%"+vo.getAccountBodyBank()+"%'");
		}
		if(StringUtils.isNotEmpty(vo.getRemarks())) {//归属银行
			sb.append("   and t.remarks like '%"+vo.getRemarks()+"%'");
		}
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(vo.getStarTime())) {
			sb.append(" AND t.TRADE_DATE >= to_date('"+vo.getStarTime()+"','yyyy-mm-dd') ");
		}
		if(StringUtils.isNotEmpty(vo.getMinMoney())) {
			sb.append(" and t.account_money>='"+vo.getMinMoney()+"' ");
		}
		if(StringUtils.isNotEmpty(vo.getMinMoney())) {
			sb.append(" and t.account_money<='"+vo.getMaxMoney()+"' ");
		}
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(vo.getEndTime())) {
			sb.append(" AND t.TRADE_DATE <= to_date('"+vo.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		sb.append(" group by t.account_body, t.account_body_number, t.account_body_card,t.account_body_bank ");
		sb.append("  ) m");
		sb.append(" left join  ");
		sb.append(" (select max(surplus_money) as surplus_money,ACCOUNT_BODY_CARD,trade_date from "+whichTable+" where casemanage_id = '"+vo.getCasemanageId()+"' group by ACCOUNT_BODY_CARD,TRADE_DATE ) n");
		sb.append(" on m.accountBodyCard = n.account_body_card and m.endDate=n.trade_date");
		if (StringUtils.isNotEmpty(sortName)){
			sb.append(" order by "+sortName+" "+sortOrder);
		}
		long count = jdbcTemplate.queryForObject(countQuery+sb.toString(),long.class);
		Query query = this.entityManager.createNativeQuery(bodyQuery+sb.toString());
		//将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		//设置分页
		query.setFirstResult((page - 1) * size);
		query.setMaxResults(size);
		//获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(count,list);
		return pageResult;
	}
	
	/**
	 * 现金交易分析
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Page<BillCashDomain> findBillCash(DealFlowVo vo,String sortName,String sortOrder,Pageable page) {
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		StringBuffer sb = new StringBuffer();
		sb.append(" select t.account_body as accountBody,");
		sb.append("       t.account_body_number as accountBodyNumber,");
		sb.append("       t.account_body_card as accountBodyCard,");
		sb.append("       count(t.bill_Id) as totalCount,");
		sb.append("       sum(abs(t.account_money)) as totalMoney,");
		sb.append("       sum(case when t.sign=0 then abs(t.account_money) else 0 end) as inMoney,");
		sb.append("       sum(case when t.sign=0 then 1 else 0 end) as inCount,");
		sb.append("       sum(case when t.sign=1 then abs(t.account_money) else 0 end) as outMoney,");
		sb.append("       sum(case when t.sign=1 then 1 else 0 end) as outCount,");
		sb.append("       min(t.trade_date) as beginDate,");
		sb.append("       max(t.trade_date) as endDate");
		StringBuffer fromSql = new StringBuffer();
		fromSql.append("  from "+whichTable+" t");
		fromSql.append(" where t.is_delete = 0");
		fromSql.append("   and t.casemanage_id = '"+vo.getCasemanageId()+"'");
		fromSql.append("   and (t.content_abstract like '%存%' or t.content_abstract like '%现%' or t.account_mode like '%存款%' or t.account_mode like '%取款%')");
		
		if(StringUtils.isNotEmpty(vo.getDetailKeyword())) {
			//对交易类型 摘要 备注 MAC地址 IP地址 查询
			fromSql.append(" and (t.content_abstract like '%"+vo.getDetailKeyword()+"%' or t.remarks like '%"+vo.getDetailKeyword()+"%' or t.mac_address like '%"+vo.getDetailKeyword()+"%' or t.ip_address like '%"+vo.getDetailKeyword()+"%') ");
		}
		
		if(StringUtils.isNotEmpty(vo.getMinMoney())) {
			fromSql.append(" and t.account_money>='"+vo.getMinMoney()+"' ");
		}
		if(StringUtils.isNotEmpty(vo.getMinMoney())) {
			fromSql.append(" and t.account_money<='"+vo.getMaxMoney()+"' ");
		}
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(vo.getStarTime())) {
			fromSql.append(" AND t.TRADE_DATE >= to_date('"+vo.getStarTime()+"','yyyy-mm-dd') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(vo.getEndTime())) {
			fromSql.append(" AND t.TRADE_DATE <= to_date('"+vo.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}

		fromSql.append(" and (1=0 ");
		if(StringUtils.isNotEmpty(vo.getCheckedAccount())) {
			if (vo.getCheckedAccount().split(",").length<1000){
				String cards = "";
				for(String str:vo.getCheckedAccount().split(",")) {
					if(StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if(StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards+"'"+accountBodyCard+"',";
						}
					}
				}
				if(cards.endsWith(",")) {
					cards = cards.substring(0,cards.length()-1);
				}
				if(cards.length()>0) {
					fromSql.append(" or t.account_body_card in ("+cards+")");
				}
			}else{
				fromSql.append(" or 1=1 ");
			}
		}
		fromSql.append(" ) ");

		fromSql.append(" group by account_body, account_body_number, account_body_card");
		String orderSql = "";
		if (org.apache.commons.lang3.StringUtils.isNotBlank(sortName)){
			orderSql = " order by "+sortName+" "+sortOrder;
		}else {
			orderSql=" order by totalMoney DESC ";
		}
		
		Query query = getEntityManager().createNativeQuery(sb.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(BillCashDomain.class));
		//封装返回数据
		return super.findPageBySql4GroupBy(BillCashDomain.class,sb.toString(),fromSql.toString(),orderSql,new LinkedList<Object>(),page);
	}

	/**
	 * 现金交易分析详情
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Map<String,Object > findBillCashDetail(DealFlowVo vo,String sortName,String sortOrder,Integer page,Integer size) {
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		StringBuffer sb = new StringBuffer();
		sb.append(" select * ");
		sb.append("  from "+whichTable+" t");
		sb.append(" where t.is_delete = 0");
		sb.append("   and t.casemanage_id = '"+vo.getCasemanageId()+"'");
		sb.append("   and (t.content_abstract like '%存%' or t.content_abstract like '%现%' or t.account_mode like '%存款%' or t.account_mode like '%取款%')");

		if(StringUtils.isNotEmpty(vo.getDetailKeyword())) {
			//对交易类型 摘要 备注 MAC地址 IP地址 查询
			sb.append(" and (t.content_abstract like '%"+vo.getDetailKeyword()+"%' or t.remarks like '%"+vo.getDetailKeyword()+"%' or t.mac_address like '%"+vo.getDetailKeyword()+"%' or t.ip_address like '%"+vo.getDetailKeyword()+"%') ");
		}

		if(StringUtils.isNotEmpty(vo.getMinMoney())) {
			sb.append(" and t.account_money>='"+vo.getMinMoney()+"' ");
		}
		if(StringUtils.isNotEmpty(vo.getMinMoney())) {
			sb.append(" and t.account_money<='"+vo.getMaxMoney()+"' ");
		}

		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(vo.getStarTime())) {
			sb.append(" AND t.TRADE_DATE >= to_date('"+vo.getStarTime()+"','yyyy-mm-dd') ");
		}

		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(vo.getEndTime())) {
			sb.append(" AND t.TRADE_DATE <= to_date('"+vo.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}

		sb.append(" AND  t.account_body_card = '"+vo.getAccountBodyCard()+"' ");

		if (org.apache.commons.lang3.StringUtils.isNotBlank(sortName)){
			sb.append(" order by "+sortName+" "+sortOrder);
		}

		List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());

		Query query = this.entityManager.createNativeQuery(sb.toString());
		//将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		//设置分页
		query.setFirstResult((page-1)*size);
		query.setMaxResults(size);
		//获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		Map<String,Object > map = new HashMap<>();
		map.put("total",Long.valueOf(maps.size()));
		map.put("rows",list);
//		PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list,maps);
		return map;
	}
	
	public void findByAccountBodyAndCasemanageId(String accountBody,String casemanageId,String nodeText,String idNumber,String personType){
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sb = new StringBuffer("update "+whichTable+" set account_body='"+nodeText+"',id_number='"+idNumber+"'");
		if(StringUtils.isNotEmpty(personType)) {
			sb.append(",body_type='"+personType+"'");
		}
		sb.append(" where account_body = '"+accountBody+"' AND casemanage_id = '"+casemanageId+"'");
		jdbcTemplate.update(sb.toString());
	}
	public void deleteByAccountBodyAndCasemanageId(String accountBody,String casemanageId){
		String whichTable = caseManageService.getWhichTable(casemanageId);
		String sql = "delete from "+whichTable+" where account_body = '"+accountBody+"' AND casemanage_id = '"+casemanageId+"'";
		jdbcTemplate.update(sql);
	}
	
	/**
	 * 根据交易主体名称、案件id查询流水记录
	 * @param accountBody
	 * @return
	 */
	public List<DealFlow> findByAccountBodyAndCasemanageId(String accountBody,String casemanageId){
		String whichTable = caseManageService.getWhichTable(casemanageId);
		String sb="SELECT * FROM "+whichTable+" WHERE account_body = '"+accountBody+"' AND casemanage_id = '"+casemanageId+"'";
		List<DealFlow> query = jdbcTemplate.query(sb, new BeanPropertyRowMapper<DealFlow>(DealFlow.class));
		return query;
	}
	
	/**
	 * 根据交易主体名称、案件id查询流水记录
	 * @param accountBody
	 * @return
	 */
	public List<DealFlow> findByAccountBodyAndCasemanageIdGroupByAccountBody(String accountBody,String casemanageId){
		String whichTable = caseManageService.getWhichTable(casemanageId);
		String sb="SELECT account_body,id_number,account_body_card FROM "+whichTable+" WHERE account_body = '"+accountBody+"' AND casemanage_id = '"+casemanageId+"' group by account_body,id_number,account_body_card";
		List<DealFlow> query = jdbcTemplate.query(sb, new BeanPropertyRowMapper<DealFlow>(DealFlow.class));
		return query;
	}
	
	/**
	 * 根据交易主体卡号名称、案件id查询流水记录
	 * @param accountBody
	 * @return
	 */
	public List<DealFlow> findByAccountBodyCardAndCasemanageIdGroupByAccountBody(String accountBodyCard,String casemanageId){
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT MAX(t.account_body) as ACCOUNT_BODY,t.account_body_card,MAX(t.id_number) as ID_NUMBER");
		sb.append(",MAX(account_body_bank) as account_body_bank  FROM "+whichTable+" t WHERE account_body_card = '"+accountBodyCard+"' ");
		sb.append(" AND casemanage_id = '"+casemanageId+"' group by account_body_card");
		List<DealFlow> query = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<DealFlow>(DealFlow.class));
		return query;
	}
	
	
	/**
	 * 根据交易主体卡号身份证号统计该主题下交易清单
	 * @param accountBody
	 * @param idNumber
	 * @param rownum
	 * @param casemanageId
	 * @return
	 */
	public List<Map<String,Object>> findPersonInformationCardsTrans(String accountBodyCard,String idNumber, String rownum,String casemanageId) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sbf = new StringBuffer();
		sbf.append("select * from (select MAX(t.account_body) as ACCOUNT_BODY,MAX(t.account_body_card) account_body_card,");
		sbf.append("(case when MAX(t.account_rival) is null then account_rival_card else MAX(t.account_rival) end)  as account_rival,");
		sbf.append("SUM(t.account_money) sum_account_money,count(1) trans_num from "+whichTable+" t ");
		sbf.append("where t.account_body_card = '"+accountBodyCard+"' and t.id_number='"+idNumber+"' ");
		sbf.append("and account_rival is not null and account_rival_card is not null group by t.account_rival_card ) t2 ");
		if(StringUtils.isNotEmpty(rownum)) {
			sbf.append("where ROWNUM <"+rownum);
		}
		return jdbcTemplate.queryForList(sbf.toString());
	}
	
	/**
	 * 根据账号身份证号分页查询流入、流出交易信息
	 * @param vo
	 * @param page
	 * @param sortName
	 * @param sortOrder
	 * @param sign
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageResult getPersionInformationCardInTransByPage(String accountBodyCard,String idNumber,
			String sign,String casemanageId, Integer page,Integer rows,String sort,String order) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT * FROM (SELECT MAX(ACCOUNT_RIVAL) as accountRival,ACCOUNT_RIVAL_CARD as accountRialCard,");
		selectSql.append("COUNT(1) AS transNum,COALESCE(sum(CASE WHEN sign = '0' THEN abs(account_money) END), 0) AS inMoney,");
		selectSql.append("COALESCE(sum(CASE WHEN sign = '1' THEN abs(account_money) END),0) AS outMoney,COUNT(CASE WHEN sign = '0' THEN 1 END) AS inNumber,");
		selectSql.append("COUNT(CASE WHEN sign = '1' THEN 1 END) AS outNumber ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" WHERE IS_DELETE=0 AND account_rival_card IS NOT NULL ");
		if(StringUtils.isNotEmpty(casemanageId)) {
			fromWhereSql.append("AND casemanage_id = '"+casemanageId+"' ");
		}
		if(StringUtils.isNotEmpty(accountBodyCard)) {
			fromWhereSql.append("AND account_body_card ='"+accountBodyCard+"' ");
		}
		if(StringUtils.isNotEmpty(idNumber)) {
			fromWhereSql.append("AND id_number ='"+idNumber+"' ");
		}
		if(StringUtils.isNotEmpty(sign)) {
			fromWhereSql.append("AND sign ='"+sign+"' ");
		}
		fromWhereSql.append("GROUP BY account_rival_card) t ");
		
		if (StringUtils.isNotEmpty(sort)){
			fromWhereSql.append(" order by "+sort+" "+order);
		}else {
			fromWhereSql.append(" ORDER BY inMoney DESC ");
		}
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a ",long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		//将查询结果集转为Map
		
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(PersonInformationCardTrans.class));
		//设置分页
		query.setFirstResult((page - 1) * rows);
		query.setMaxResults(rows);
		//获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(count,list);
		return pageResult;
	}
	
	/**
	 * 根据账号身份证号分页查询交易信息
	 * @param vo
	 * @param page
	 * @param sortName
	 * @param sortOrder
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageResult getPersionInformationCardTransByPage(String accountBodyCard,String idNumber,
			String casemanageId, Integer page,Integer rows,String sort,String order) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select MAX(t.account_body) as accountBody,t.account_body_card as accountBodyCard,");
		selectSql.append("(case when MAX(t.account_rival) is null then account_rival_card else MAX(t.account_rival) end)  as accountRival,");
		selectSql.append("SUM(t.account_money) sumAccountMoney,count(1) transNum");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" FROM "+whichTable+" t where t.IS_DELETE=0 ");
		if(StringUtils.isNotEmpty(casemanageId)) {
			fromWhereSql.append("AND t.casemanage_id = '"+casemanageId+"' ");
		}
		if(StringUtils.isNotEmpty(accountBodyCard)) {
			fromWhereSql.append("AND t.account_body_card ='"+accountBodyCard+"' ");
		}
		if(StringUtils.isNotEmpty(idNumber)) {
			fromWhereSql.append("AND t.id_number ='"+idNumber+"' ");
		}
		fromWhereSql.append("AND t.account_rival_card is not null AND t.account_rival is not null group by t.account_body_card,t.account_rival_card ");
		if (StringUtils.isNotEmpty(sort)){
			fromWhereSql.append(" order by "+sort+" "+order);
		}else {
			fromWhereSql.append(" ORDER BY sumAccountMoney DESC ");
		}
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a ",long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(PersonInformationCardTrans.class));
		//设置分页
		query.setFirstResult((page - 1) * rows);
		query.setMaxResults(rows);
		//获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(count,list);
		return pageResult;
	}
	
	
	/**
	 * 根据卡号、身份证号统计流入流出交易
	 * @param accountBodyCard
	 * @param idNumber
	 * @param rownum
	 * @param casemanageId
	 * @return
	 */
	public List<Map<String,Object>> findPersonInformationCardsInTrans(String accountBodyCard,String idNumber,
			String rownum,String casemanageId,String sign) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sbf = new StringBuffer();
		sbf.append("SELECT * FROM (SELECT MAX(ACCOUNT_RIVAL) as ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD,SUM(abs(account_money)) AS totalMoney,");
		sbf.append("COUNT(1) AS totalNumber,COALESCE(sum(CASE WHEN sign = '0' THEN abs(account_money) END), 0) AS inMoney,");
		sbf.append("COALESCE(sum(CASE WHEN sign = '1' THEN abs(account_money) END),0) AS outMoney,COUNT(CASE WHEN sign = '0' THEN 1 END) AS inNumber,");
		sbf.append("COUNT(CASE WHEN sign = '1' THEN 1 END) AS outNumber FROM "+whichTable);
		sbf.append(" WHERE account_rival_card IS NOT NULL AND casemanage_id = '"+casemanageId+"' ");
		sbf.append("AND account_body_card ='"+accountBodyCard+"' AND id_number ='"+idNumber+"' ");
		sbf.append("AND sign ='"+sign+"'");
		sbf.append("GROUP BY account_rival_card) t ");
		if(StringUtils.isNotEmpty(rownum)) {
			sbf.append("where ROWNUM <"+rownum);
		}
		return jdbcTemplate.queryForList(sbf.toString());
	}
	
	/**
	 * 根据交易主体名称查询流水记录
	 * @param accountBody
	 * @return
	 */
	public DealFlow findFirstByAccountBody(String accountBody,String caseId) {
		String whichTable = caseManageService.getWhichTable(caseId);
		String sql = "select * from "+whichTable+" where casemanage_id='"+caseId+"' and is_delete=0 and account_body='"+accountBody+"' and rownum<2";
		List<DealFlow> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<DealFlow>(DealFlow.class));
		if(list!=null && list.size()>0) {
			return list.get(0);
		}
		return null;
	}
	
	/**
	 * 根据身份证信息、条数查询卡号所属银行余额等信息
	 * @param caseId
	 * @param rownum
	 * @param idNumber
	 * @return List<PersonCards>
	 */
	public List<PersonCards> findPersonCards(String caseId, String rownum,String idNumber,List<String> cards){
		String whichTable = caseManageService.getWhichTable(caseId);
		StringBuffer sbf = new StringBuffer();
		sbf.append("select t2.* from (select t.account_body_card as accountBodyCard,max(t.account_body_bank) as accountBodyBank,");
		sbf.append("count(1) as tranNumber from "+whichTable +" t where 1=1 ");
		if(StringUtils.isNotEmpty(idNumber)) {
			sbf.append(" and t.id_number = '"+idNumber+"' ");
		}
		if(cards != null && cards.size() > 0 ) {
			sbf.append(" and t.account_body_card in('"+StringUtils.join(cards,"','")+"') ");
		}
		sbf.append(" group by t.account_body_card )t2 ");
		if(StringUtils.isNotEmpty(rownum)) {
			sbf.append("where rownum < "+rownum);
		}
		List<PersonCards> list = jdbcTemplate.query(sbf.toString(), new BeanPropertyRowMapper<PersonCards>(PersonCards.class));
		if(null != list && list.size() > 0) {
			for(PersonCards pc : list) {
				String sql = "select t.surplus_money as SURPLUS_MONEY from "+whichTable +" t where "
						+ "t.trade_date = (select MAX(t2.trade_date) from "+whichTable +" t2"
						+ " where t2.account_body_card='"+pc.getAccountBodyCard()+"')\r\n" + 
						"and t.account_body_card='"+pc.getAccountBodyCard()+"' and rownum =1";
				List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
				if(maps != null && maps.size() > 0) {
					pc.setBalance((BigDecimal)maps.get(0).get("SURPLUS_MONEY"));
				}
			}
		}
		return list;
	}
	
	/**
	 * 根据账号身份证号分页查询交易信息
	 * @param vo
	 * @param page
	 * @param sortName
	 * @param sortOrder
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageResult getPersionInformationCardsTransByPage(String idNumber,String caseId, Integer page,
			Integer rows,String sort,String order,List<String> cards) {
		String whichTable = caseManageService.getWhichTable(caseId);
		StringBuffer selectSql = new StringBuffer();
		
		selectSql.append("select t2.* from (select t.account_body_card as accountBodyCard,max(t.account_body_bank) as accountBodyBank,");
		selectSql.append("count(1) as tranNumber ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" FROM "+whichTable+" t where t.IS_DELETE=0 ");
		if(StringUtils.isNotEmpty(idNumber)) {
			fromWhereSql.append("AND t.id_number = '"+idNumber+"' ");
		}
		if(cards != null && cards.size() > 0 ) {
			fromWhereSql.append("AND t.account_body_card in('"+StringUtils.join(cards,"','")+"') ");
		}
		fromWhereSql.append(" group by t.account_body_card )t2 ");
		if (StringUtils.isNotEmpty(sort)){
			fromWhereSql.append(" order by "+sort+" "+order);
		}else {
			fromWhereSql.append(" ORDER BY tranNumber DESC ");
		}
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a", long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(PersonCards.class));
		// 设置分页
		query.setFirstResult((page - 1) * rows);
		query.setMaxResults(rows);
		// 获取查询结果集
		List<PersonCards> list = query.getResultList();
		if(null != list && list.size() > 0) {
			for(PersonCards pc : list) {
				String sql = "select t.surplus_money as SURPLUS_MONEY from "+whichTable +" t where "
						+ "t.trade_date = (select MAX(t2.trade_date) from "+whichTable +" t2"
						+ " where t2.account_body_card='"+pc.getAccountBodyCard()+"')\r\n" + 
						"and t.account_body_card='"+pc.getAccountBodyCard()+"' and rownum =1";
				Map<String, Object> maps = jdbcTemplate.queryForMap(sql);
				if(maps != null ) {
					pc.setBalance((BigDecimal)maps.get("SURPLUS_MONEY"));
				}
			}
		}
		PageResult pageResult = new PageResult(count, list);
		//封装返回数据
		return pageResult;
	}

	/**
	 *	根据手机号获取支付宝交易信息
	 * @return
	 */
	public List<Map<String ,Object>> findPersonInformationAlipay(List<String> tel,String rownum){
		List<String> alipayNumber = alipayService.findByTelephone(tel);
		StringBuffer sbf = new StringBuffer();
		sbf.append("select * from (select MAX(p.account_body) as accountBody,MAX(p.account_body_card) as accountBodyCard,");
		sbf.append("p.account_rival_card as accountRivalCard,MAX(p.account_rival) as accountRival,sum(ACCOUNT_MONEY) as totalMoney,");
		sbf.append("COUNT(1) as accountNumber from TB_DEAL_FLOW p where p.ACCOUNT_BODY_CARD in('"+StringUtils.join(alipayNumber,"','")+"') ");
		sbf.append(" group by account_body_card,account_rival_card) a ");
		if(StringUtils.isNotEmpty(rownum)) {
			sbf.append("where ROWNUM <"+rownum);
		}
		return jdbcTemplate.queryForList(sbf.toString());
	}
	/**
	 *	根据手机号获取微信交易信息
	 * @return
	 */
	public List<Map<String ,Object>> findPersonInformationTencentWechat(List<String> tel,String rownum){
		List<String> wechatNumber = tencentWechatService.findByTelephone(tel);
		StringBuffer sbf = new StringBuffer();
		sbf.append("select * from (select MAX(p.account_body) as accountBody,MAX(p.account_body_card) as accountBodyCard,");
		sbf.append("p.account_rival_card as accountRivalCard,MAX(p.account_rival) as accountRival,sum(ACCOUNT_MONEY) as totalMoney,");
		sbf.append("COUNT(1) as accountNumber from TB_DEAL_FLOW p where p.ACCOUNT_BODY_CARD in('"+StringUtils.join(wechatNumber,"' ,'")+"') ");
		sbf.append(" group by account_body_card,account_rival_card) a ");
		if(StringUtils.isNotEmpty(rownum)) {
			sbf.append("where ROWNUM <"+rownum);
		}
		return jdbcTemplate.queryForList(sbf.toString());
	}
    /**
     *	根据手机号获取qq交易信息
     * @return
     */
    public List<Map<String ,Object>> findPersonInformationTencentQQ(List<String> tel,String rownum){
        List<String> qqNumber = tencentQQService.findByTelephone(tel);
        StringBuffer sbf = new StringBuffer();
        sbf.append("select * from (select MAX(p.account_body) as accountBody,MAX(p.account_body_card) as accountBodyCard,");
        sbf.append("account_rival_card as accountRivalCard,MAX(p.account_rival) as accountRival,sum(ACCOUNT_MONEY) as totalMoney,");
        sbf.append("COUNT(1) as accountNumber from TB_DEAL_FLOW p where p.ACCOUNT_BODY_CARD in('"+StringUtils.join(qqNumber,"' ,'")+"') ");
        sbf.append(" group by account_body_card,account_rival_card) a ");
        if(StringUtils.isNotEmpty(rownum)) {
            sbf.append("where ROWNUM <"+rownum);
        }
        return jdbcTemplate.queryForList(sbf.toString());
    }
    
    /**
	 *	根据手机号获取微信交易信息page
	 * @return
	 */
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public PageResult findPersonInformationTencentWechatEasyUi(List<String> tel,Integer page,Integer rows,String sort,String order){
		List<String> wechatNumber = tencentWechatService.findByTelephone(tel);
        List<DealFlow> list = null;
        long count = 0;
        if(wechatNumber != null && wechatNumber.size() > 0) {
        	StringBuffer sbf = new StringBuffer();
    		sbf.append("select * from (select MAX(p.account_body) as accountBody,MAX(p.account_body_card) as accountBodyCard,");
    		sbf.append("p.account_rival_card as accountRivalCard,MAX(p.account_rival) as accountRival,sum(ACCOUNT_MONEY) as totalMoney,");
    		sbf.append("COUNT(1) as totalCount from TB_DEAL_FLOW p where p.ACCOUNT_BODY_CARD in('"+StringUtils.join(wechatNumber,"' ,'")+"') ");
    		sbf.append(" group by account_body_card,account_rival_card) a ");
        	if (StringUtils.isNotEmpty(sort) && StringUtils.isNotEmpty(order)){
        		sbf.append(" ORDER BY " + sort + " " + order);
    		}else{
    			sbf.append(" ORDER BY totalMoney ");
    		}
        	count = jdbcTemplate.queryForObject("select count(1) from ("+sbf.toString()+") a ", long.class);
        	Query query = this.entityManager.createNativeQuery(sbf.toString());
        	// 将查询结果集转为Map
        	query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(BillCashDomain.class));
        	// 设置分页
        	query.setFirstResult((page - 1) * rows);
        	query.setMaxResults(rows);
        	// 获取查询结果集
        	list = query.getResultList();
        }
		PageResult pageResult = new PageResult(count, list);
		return pageResult;
	}
    
    /**
     *	根据手机号获取微信交易信息page
     * @return
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public PageResult findPersonInformationTencentAliPayEasyUi(List<String> tel,Integer page,Integer rows,String sort,String order){
    	List<String> alipayNumber = alipayService.findByTelephone(tel);
    	List<DealFlow> list = null;
    	long count = 0;
    	if(alipayNumber != null && alipayNumber.size() > 0) {
    		StringBuffer sbf = new StringBuffer();
    		sbf.append("select * from (select MAX(p.account_body) as accountBody,MAX(p.account_body_card) as accountBodyCard,");
    		sbf.append("p.account_rival_card as accountRivalCard,MAX(p.account_rival) as accountRival,sum(ACCOUNT_MONEY) as totalMoney,");
    		sbf.append("COUNT(1) as totalCount from TB_DEAL_FLOW p where p.ACCOUNT_BODY_CARD in('"+StringUtils.join(alipayNumber,"','")+"') ");
    		sbf.append(" group by account_body_card,account_rival_card) a ");
    		if (StringUtils.isNotEmpty(sort) && StringUtils.isNotEmpty(order)){
    			sbf.append(" ORDER BY " + sort + " " + order);
    		}else{
    			sbf.append(" ORDER BY totalMoney ");
    		}
    		count = jdbcTemplate.queryForObject("select count(1) from ("+sbf.toString()+") a ", long.class);
    		Query query = this.entityManager.createNativeQuery(sbf.toString());
    		// 将查询结果集转为Map
    		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(BillCashDomain.class));
    		// 设置分页
    		query.setFirstResult((page - 1) * rows);
    		query.setMaxResults(rows);
    		// 获取查询结果集
    		list = query.getResultList();
    	}
    	PageResult pageResult = new PageResult(count, list);
    	return pageResult;
    }
    
    /**
     *	根据手机号获取qq交易信息eas
     * @return
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public PageResult findPersonInformationTencentQQEasyUi(List<String> tel,Integer page,Integer rows,String sort,String order){
        List<String> qqNumber = tencentQQService.findByTelephone(tel);
        List<DealFlow> list = null;
        long count = 0;
        if(qqNumber != null && qqNumber.size() > 0) {
        	StringBuffer sbf = new StringBuffer();
        	sbf.append("select * from (select MAX(p.account_body) as accountBody,MAX(p.account_body_card) as accountBodyCard,");
        	sbf.append("account_rival_card as accountRivalCard,MAX(p.account_rival) as accountRival,sum(ACCOUNT_MONEY) as totalMoney,");
        	sbf.append("COUNT(1) as totalCount from TB_DEAL_FLOW p where p.ACCOUNT_BODY_CARD in('"+StringUtils.join(qqNumber,"' ,'")+"') ");
        	sbf.append(" group by account_body_card,account_rival_card) a ");
        	if (StringUtils.isNotEmpty(sort) && StringUtils.isNotEmpty(order)){
        		sbf.append(" ORDER BY " + sort + " " + order);
    		}else{
    			sbf.append(" ORDER BY totalMoney ");
    		}
        	count = jdbcTemplate.queryForObject("select count(1) from ("+sbf.toString()+") a ", long.class);
        	Query query = this.entityManager.createNativeQuery(sbf.toString());
        	// 将查询结果集转为Map
        	query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(BillCashDomain.class));
        	// 设置分页
        	query.setFirstResult((page - 1) * rows);
        	query.setMaxResults(rows);
        	// 获取查询结果集
        	list = query.getResultList();
        	
        }
		PageResult pageResult = new PageResult(count, list);
		return pageResult;
    }
	/**
	 * 根据手机号分页查询支付宝交易信息
	 * @param tel
	 * @param page
	 * @param sortName
	 * @param sortOrder
	 * @return
	 */
	public Page<DealFlow> getPersionInformationAlipayByPage(List<String> tel, Pageable page,String sortName,String sortOrder,String caseId){
		List<String> alipayNumber = alipayService.findByTelephone(tel);
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select MAX(p.account_body) as accountBody,p.account_body_card as accountBodyCard,");
		selectSql.append("p.account_rival_card as accountRivalCard,MAX(p.account_rival) as accountRival,sum(ACCOUNT_MONEY) as accountMoney,COUNT(1) as isDelete");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" from TB_DEAL_FLOW p where isDelete=0  and DATA_SOURCES = 2 and CASEMANAGE_ID = '"+caseId+"'");
		if(alipayNumber!=null&&alipayNumber.size()>0) {
			fromWhereSql.append(" AND p.ACCOUNT_BODY_CARD in('"+StringUtils.join(alipayNumber,"','")+"') ");
		}
		fromWhereSql.append(" group by p.account_body_card,p.account_rival_card");
		String orderBySql = "";
		if (StringUtils.isNotEmpty(sortName)){
			orderBySql =" order by "+sortName+" "+sortOrder;
		}else {
			orderBySql = " order by isDelete DESC ";
		}
		Page<DealFlow> pagePersonCards = super.findPageBySql4GroupBy(DealFlow.class,selectSql.toString(), fromWhereSql.toString(), orderBySql, params, page);

		//封装返回数据
		return pagePersonCards;
	}
	/**
	 * 根据手机号分页查询微信交易信息
	 * @param tel
	 * @param page
	 * @param sortName
	 * @param sortOrder
	 * @return
	 */
	public Page<DealFlow> getPersionInformationTencentWechatByPage(List<String> tel, Pageable page,String sortName,String sortOrder,String caseId){
		List<String> wechatNumber = tencentWechatService.findByTelephone(tel);
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select MAX(p.account_body) as accountBody,p.account_body_card as accountBodyCard,");
		selectSql.append("p.account_rival_card as accountRivalCard,MAX(p.account_rival) as accountRival,sum(ACCOUNT_MONEY) as accountMoney,COUNT(1) as isDelete");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" from TB_DEAL_FLOW p where isDelete=0 and DATA_SOURCES = 1  and CASEMANAGE_ID = '"+caseId+"'");
		if(wechatNumber!=null&&wechatNumber.size()>0) {
			fromWhereSql.append(" AND p.ACCOUNT_BODY_CARD in('"+StringUtils.join(wechatNumber,"','")+"') ");
		}
		fromWhereSql.append(" group by p.account_body_card,p.account_rival_card");
		String orderBySql = "";
		if (StringUtils.isNotEmpty(sortName)){
			orderBySql =" order by "+sortName+" "+sortOrder;
		}else {
			orderBySql = " order by isDelete DESC ";
		}
		Page<DealFlow> pagePersonCards = super.findPageBySql4GroupBy(DealFlow.class,selectSql.toString(), fromWhereSql.toString(), orderBySql, params, page);

		//封装返回数据
		return pagePersonCards;
	}
    /**
     * 根据手机号分页查询qq交易信息
     * @param tel
     * @param page
     * @param sortName
     * @param sortOrder
     * @return
     */
    public Page<DealFlow> getPersionInformationTencentQQByPage(List<String> tel, Pageable page,String sortName,String sortOrder,String caseId){
        List<String> qqNumber = tencentQQService.findByTelephone(tel);
        List<Object> params = new LinkedList<Object>();
        StringBuffer selectSql = new StringBuffer();
        selectSql.append("select MAX(p.account_body) as accountBody,p.account_body_card as accountBodyCard,");
        selectSql.append("p.account_rival_card as accountRivalCard,MAX(p.account_rival) as accountRival,sum(ACCOUNT_MONEY) as accountMoney,COUNT(1) as isDelete");
        StringBuffer fromWhereSql = new StringBuffer();
        fromWhereSql.append(" from TB_DEAL_FLOW p where isDelete=0 and DATA_SOURCES = 0  and CASEMANAGE_ID = '"+caseId+"'");
        if(qqNumber!=null&&qqNumber.size()>0) {
            fromWhereSql.append(" AND p.ACCOUNT_BODY_CARD in('"+StringUtils.join(qqNumber,"','")+"') ");
        }
        fromWhereSql.append(" group by p.account_body_card,p.account_rival_card");
        String orderBySql = "";
        if (StringUtils.isNotEmpty(sortName)){
            orderBySql =" order by "+sortName+" "+sortOrder;
        }else {
            orderBySql = " order by isDelete DESC ";
        }
        Page<DealFlow> pagePersonCards = super.findPageBySql4GroupBy(DealFlow.class,selectSql.toString(), fromWhereSql.toString(), orderBySql, params, page);

        //封装返回数据
        return pagePersonCards;
    }
	/**
	 * 根据账号身份证号分页查询交易信息
	 * @param vo
	 * @param page
	 * @param sortName
	 * @param sortOrder
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageResult getPersionInformationPhonesByPage(String idNumber, Integer page,Integer rows, String sort, String order) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select * from(select p.service_number as serviceNumber,MAX(p.service_location) as serviceLocation,COUNT(1) as isDelete");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" from tb_phone_recording p where 1=1 ");
		if(StringUtils.isNotEmpty(idNumber)) {
			fromWhereSql.append("AND p.id_number = '"+idNumber+"' ");
		}
		fromWhereSql.append(" group by p.service_number ) t2 ");
		if (StringUtils.isNotEmpty(sort)){
			fromWhereSql.append(" order by "+sort+" "+order);
		}else {
			fromWhereSql.append(" order by isDelete DESC ");
		}
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a", long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(PhoneRecord.class));
		// 设置分页
		query.setFirstResult((page - 1) * rows);
		query.setMaxResults(rows);
		// 获取查询结果集
		List<PhoneRecord> list = query.getResultList();
		PageResult pageResult = new PageResult(count, list);
		//封装返回数据
		return pageResult;
	}
	
	/**
	 * 根据身份证查询话单信息
	 * @param idNumber
	 * @param rownum
	 * @return
	 */
	public List<Map<String,Object>> findPersonInformationPhone(String idNumber, String rownum) {
		StringBuffer sbf = new StringBuffer();
		sbf.append("select p.service_number as serviceNumber,MAX(p.service_location) as serviceLocation,");
		sbf.append("COUNT(1) as callNumber from tb_phone_recording p where p.id_number='"+idNumber+"' ");
		if(StringUtils.isNotEmpty(rownum)) {
			sbf.append("and ROWNUM <"+rownum);
		}
		sbf.append(" group by p.service_number");
		return jdbcTemplate.queryForList(sbf.toString());
	}
	/**
	 * 根据案件id删除所有记录
	 * @param casemanageId
	 */
	public void deleteByCasemanageId(String casemanageId) {
//		String whichTable = caseManageService.getWhichTable(casemanageId);
		String sql= "delete from tb_deal_flow where CASEMANAGE_ID = "+ casemanageId;
		jdbcTemplate.execute(sql);
	}
	
	public void findByAccountBodyCardAndCasemanageId(List<String> accountBodyCards, String casemanageId,String nodeText,String idNumber,String personType){
		String whichTable = caseManageService.getWhichTable(casemanageId);
		String cards = "";
		for(String account : accountBodyCards) {
			cards += "'"+account+"',";
		}
		if(cards.endsWith(",")) {
			cards = cards.substring(0, cards.length() -1);
		}
		StringBuffer sb = new StringBuffer("update "+whichTable+" set account_body='"+nodeText+"',id_number='"+idNumber+"'");
		if(StringUtils.isNotEmpty(personType)) {
			sb.append(",body_type='"+personType+"' ");
		}
		sb.append(" where account_body_card IN("+cards+") AND casemanage_id = '"+casemanageId+"'");
		jdbcTemplate.update(sb.toString());
	}
	public List<DealFlow> findByAccountBodyCardAndCasemanageId(List<String> accountBodyCards, String casemanageId){
		String whichTable = caseManageService.getWhichTable(casemanageId);
		String cards = "";
		for(String account : accountBodyCards) {
			cards += "'"+account+"',";
		}
		if(cards.endsWith(",")) {
			cards = cards.substring(0, cards.length() -1);
		}
		String sql = "select * from "+whichTable+" where account_body_card IN("+cards+") AND casemanage_id = '"+casemanageId+"'";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<DealFlow>(DealFlow.class));
	}
	
	public void deleteByAccountBodyCardAndCasemanageId(List<String> accountBodyCards, String casemanageId){
		String whichTable = caseManageService.getWhichTable(casemanageId);
		String cards = "";
		for(String account : accountBodyCards) {
			cards += "'"+account+"',";
		}
		if(cards.endsWith(",")) {
			cards = cards.substring(0, cards.length() -1);
		}
		String sql = "delete from "+whichTable+"  where account_body_card IN("+cards+") AND casemanage_id = '"+casemanageId+"'";
		jdbcTemplate.update(sql);
	}

	/**
	 * 得到交易卡号的集合
	 * @return
	 */
	public List<String> findByAccountBodyCardList(String casemanageId){
		String whichTable = caseManageService.getWhichTable(casemanageId);
		String sql="SELECT DISTINCT ACCOUNT_BODY_CARD FROM  "+whichTable+" WHERE ACCOUNT_BODY_CARD IS NOT NULL AND casemanage_id = "+casemanageId;
		List<String> query = jdbcTemplate.queryForList(sql, String.class);
		return query;
	}
	/**
	 * @Author 刘中华
	 * @Date 2019/1/15 14:43
	 * @Param [whereMap]
	 * @return java.util.List
	 * @Description 判断是否有相关的账单
	 **/
	@SuppressWarnings("rawtypes")
	public boolean isRelatedBill(Map whereMap){
		String whichTable = caseManageService.getWhichTable(whereMap.get("CASEMANAGE_ID").toString());
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT * FROM "+whichTable+"  WHERE 1=1 ");
		if (whereMap.get("CASEMANAGE_ID")!=null){
			sb.append(" AND CASEMANAGE_ID = '" + whereMap.get("CASEMANAGE_ID")+"'");
		}
		if (whereMap.get("ACCOUNT_BODY_CARD")!=null){
			sb.append(" AND ACCOUNT_BODY_CARD = '" + whereMap.get("ACCOUNT_RIVAL_CARD")+"'");
		}
		List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
		return maps.size()>0;
	}

	/**
	 * @Author 刘中华
	 * @Date 2019/2/20 11:34
	 * @Param [caseManageId]
	 * @return java.util.List
	 * @Description
	 **/
	@SuppressWarnings("unused")
	public void delRepeatData(String caseManageId){
		String whichTable = caseManageService.getWhichTable(caseManageId);
		long l = System.currentTimeMillis();
		StringBuffer sb=new StringBuffer();
		sb.append(" DELETE FROM "+whichTable+" b WHERE CASEMANAGE_ID = '"+caseManageId+"' ");
		sb.append(" AND NOT EXISTS ( SELECT a.BILL_ID FROM (SELECT min( BILL_ID ) BILL_ID FROM "+whichTable+" WHERE CASEMANAGE_ID = '"+caseManageId+"' ");
		sb.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_NUMBER,ACCOUNT_BODY_CARD,SIGN,TRADE_DATE,ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD,ACCOUNT_RIVAL_NUMBER,ACCOUNT_MONEY,SURPLUS_MONEY,ID_NUMBER,CONTENT_ABSTRACT ");
		sb.append(" ) a WHERE a.BILL_ID=b.BILL_ID )");
		jdbcTemplate.execute(sb.toString());
		long l2 = System.currentTimeMillis();
	}
	
	/**
	 * 根据uuid删除本次导入流水信息
	 * @param uuid
	 */
	public void deleteFlow4Uuid(String caseManageId,String uuid) {
		String whichTable = caseManageService.getWhichTable(caseManageId);
		String sql ="delete from "+whichTable+" where uuid='"+uuid+"' and casemanage_id='"+caseManageId+"' ";
		jdbcTemplate.update(sql);
	}
	
	/**
	 * 根据案件id查询账单涉嫌总金额、流入金额、流出金额
	 * @return
	 */
	@Cacheable(value= CacheableConfig.BILL_SHOUYE,key="'findBillSummary'+#caseManagerId")
	public Map<String, Object> findBillSummary(String caseManagerId) {
		String whichTable = caseManageService.getWhichTable(caseManagerId);
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT SUM(abs(t.account_money)) AS totalMoney,");
		sb.append("COALESCE(sum(CASE WHEN sign = '0' THEN abs(account_money) END),0) AS inMoney,");
		sb.append("COALESCE(sum(CASE WHEN sign = '1' THEN abs(account_money) END),0) AS outMoney ");
		sb.append("from "+whichTable+" t where ");
		sb.append("t.casemanage_id = '"+caseManagerId+"'");
		return jdbcTemplate.queryForMap(sb.toString());
	}
	
	/**
	 * 根据案件id查询共同交易人
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Cacheable(value= CacheableConfig.BILL_SHOUYE,key="'findCommonAccountByCaseManagerId'+#caseManagerId")
	public List<CommonStatistics> findCommonAccountByCaseManagerId(String caseManagerId) {
		String whichTable = caseManageService.getWhichTable(caseManagerId);
		StringBuffer sb = new StringBuffer();
		sb.append("select * from (select a.name as commonAccountRival, count(a.rival) as occurrences\r\n" + 
				"  from (select distinct (case\r\n" + 
				"                          when a.account_body is not null then\r\n" + 
				"                           a.account_body\r\n" + 
				"                          else\r\n" + 
				"                           a.account_body_card\r\n" + 
				"                        end) as name,\r\n" + 
				"                        b.name as rival\r\n" + 
				"          from "+whichTable+" a,\r\n" + 
				"               (select distinct (case\r\n" + 
				"                                  when account_body is not null then\r\n" + 
				"                                   account_body\r\n" + 
				"                                  else\r\n" + 
				"                                   account_body_card\r\n" + 
				"                                end) as name,\r\n" + 
				"                                account_body_card\r\n" + 
				"                  from "+whichTable+"\r\n" + 
				"                 where casemanage_id = '"+caseManagerId+"') b\r\n" + 
				"         where a.account_rival_card = b.account_body_card\r\n" + 
				"           and casemanage_id = '"+caseManagerId+"') a\r\n" + 
				" group by a.name\r\n" + 
				" order by occurrences desc) t_d WHERE rownum < 11");
		Query query = getEntityManager().createNativeQuery(sb.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		//封装返回数据
		return query.getResultList();
	}
	
	/**
	 * 根据案件id查询交易金额占比
	 * @return
	 */
	@Cacheable(value= CacheableConfig.BILL_SHOUYE,key="'findTMoneyForByCaseManagerId'+#caseManagerId")
	public List<Map<String, Object>> findTMoneyForByCaseManagerId(String caseManagerId) {
		String whichTable = caseManageService.getWhichTable(caseManagerId);
		StringBuffer sb = new StringBuffer();
		sb.append("select df.a1 as ACCOUNT,ROUND(df.totalAccountMoney / t2.totalMoney * 100, 2) AS moneyPer ");
		sb.append("from (SELECT (case when max(account_body) is not null then max(account_body) else max(account_body_card) end) as a1,");
		sb.append("SUM(abs(account_money)) AS totalAccountMoney FROM "+whichTable );
		sb.append(" WHERE account_rival_card IS NOT NULL ");
		sb.append("AND casemanage_id = '"+caseManagerId+"' ");
		sb.append("GROUP BY (case when account_body is not null then account_body else account_body_card end)) df,");
		sb.append("(SELECT sum(abs(account_money)) AS totalMoney from "+whichTable +" t ");
		sb.append("where t.casemanage_id = '"+caseManagerId+"') t2 ");
		sb.append("order by moneyPer desc");
		return jdbcTemplate.queryForList(sb.toString());
	}
	
	/**
	 * 根据交易主体名称查询详情
	 * @param sortName
	 * @param sortOrder
	 * @param caseManagerId
	 * @param accountBody
	 * @param pageable
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Page<BillStatusDomain> findEcDetailsHeadQuarter(String sortName,String sortOrder, String caseManagerId
			,String accountBody, Pageable pageable) {
		String whichTable = caseManageService.getWhichTable(caseManagerId);
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select c.rival as accountBody,\r\n" + 
				"       count(c.account_money) as totalCount,\r\n" + 
				"       sum(abs(c.account_money)) as totalMoney,\r\n" + 
				"       sum(abs(case\r\n" + 
				"                 when c.sign = 0 then\r\n" + 
				"                  c.account_money\r\n" + 
				"                 else\r\n" + 
				"                  0\r\n" + 
				"               end)) as inMoney,\r\n" + 
				"       sum(abs(case\r\n" + 
				"                 when c.sign = 1 then\r\n" + 
				"                  c.account_money\r\n" + 
				"                 else\r\n" + 
				"                  0\r\n" + 
				"               end)) as outMoney\r\n" + 
				"  from (select (case\r\n" + 
				"                 when a.account_body is not null then\r\n" + 
				"                  a.account_body\r\n" + 
				"                 else\r\n" + 
				"                  a.account_body_card\r\n" + 
				"               end) as name,\r\n" + 
				"               b.name as rival,\r\n" + 
				"               a.sign,\r\n" + 
				"               a.account_money\r\n" + 
				"          from "+whichTable+" a,\r\n" + 
				"               (select distinct (case\r\n" + 
				"                                  when account_body is not null then\r\n" + 
				"                                   account_body\r\n" + 
				"                                  else\r\n" + 
				"                                   account_body_card\r\n" + 
				"                                end) as name,\r\n" + 
				"                                account_body_card\r\n" + 
				"                  from "+whichTable+" ");
		selectSql.append(" where casemanage_id = ?) b " + 
				"			  where a.account_rival_card = b.account_body_card " + 
				"			and a.casemanage_id = ? " + 
				"			     and (case   " + 
				"			           when a.account_body is not null then " + 
				"			           a.account_body " + 
				"			           else " + 
				"			           a.account_body_card " + 
				"			         end) = ?) c " + 
				"			group by c.rival ");
		params.add(caseManagerId);
		params.add(caseManagerId);
		params.add(accountBody);
		String orderBySql = "";
		if (StringUtils.isNotEmpty(sortName)){
			orderBySql =" order by "+sortName+" "+sortOrder;
		}else {
			orderBySql = " order by totalMoney desc ";
		}
		// 查询总条数
		Query countQuery = getEntityManager().createNativeQuery("SELECT count(1) as total from (" + selectSql.toString()+") a");
		if (!params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				countQuery.setParameter(i+1, params.get(i));
			}
		}
		Number total = (Number) countQuery.getSingleResult();
		int realPageNum = pageable.getPageNumber();
		
		// 查询分页内容
		Query contentQuery = getEntityManager().createNativeQuery(selectSql + orderBySql);
		if (!params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				contentQuery.setParameter(i+1, params.get(i));
			}
		}
		contentQuery.setFirstResult(realPageNum * pageable.getPageSize());
		contentQuery.setMaxResults(pageable.getPageSize());
		contentQuery.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(BillStatusDomain.class));
		List<BillStatusDomain> content = contentQuery.getResultList();
		PageSupport newPageSupport = new PageSupport();
		newPageSupport.setPageNumber(realPageNum);
		newPageSupport.setPageSize(pageable.getPageSize());
		// 封装并返回
		return new PageImpl<BillStatusDomain>(content, pageable, total.longValue());
	}
	
	
	/**
	 * 根据交易主体名称查询详情
	 * @param sortName
	 * @param sortOrder
	 * @param caseManagerId
	 * @param accountBody
	 * @param pageable
	 * @return
	 */
	public Page<DealFlow> findEcDetails4Zero(String sortName,String sortOrder, String caseManagerId
			,String accountBody, Pageable pageable) {
		String whichTable = caseManageService.getWhichTable(caseManagerId);
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT df.bill_id AS billId, df.account_body AS accountBody, df.account_body_number AS accountBodyNumber ,df.account_body_card AS accountBodyCard,df.sign AS sign,df.trade_date AS tradeDate,df.account_rival AS accountRival,df.account_rival_number AS accountRivalNumber,df.account_rival_card AS accountRivalCard,df.account_money AS accountMoney,df.surplus_money AS surplusMoney,df.content_abstract AS contentAbstract,df.account_body_bank AS accountBodyBank,df.account_rival_bank AS accountRivalBank,df.account_address AS accountAddress,df.account_mode AS accountMode,df.serial_number AS serialNumber,df.mac_address AS macAddress,df.ip_address AS ipAddress,df.currency AS currency,df.remarks AS remarks");
		StringBuffer fromWhereSql = new StringBuffer();

		fromWhereSql.append(" FROM "+whichTable+" df ");
		fromWhereSql.append(" WHERE df.CASEMANAGE_ID = ? ");
		params.add(caseManagerId);
		if(StringUtils.isNotEmpty(accountBody)) {
			fromWhereSql.append(" AND (df.ACCOUNT_BODY = ? ");
			fromWhereSql.append(" OR df.ACCOUNT_BODY_CARD = ? ) ");
			params.add(accountBody);
			params.add(accountBody);
		}
		
		String orderBySql = null;
		if (StringUtils.isNotEmpty(sortName)){
			orderBySql =" order by "+sortName+" "+sortOrder;
		}else {
			orderBySql = " ORDER BY tradeDate DESC ";
		}

		//封装返回数据
		return super.findPageBySql(DealFlow.class,selectSql.toString(), fromWhereSql.toString(), orderBySql, params, pageable);
	}
	/**
     * 总D3统计
     * @param dealFlowdto
     * @return
     */
	@Cacheable(value= CacheableConfig.BILL_SHOUYE,key="'getInStreamAccountD3'+#casemanageId")
    public Map<String, Object> getInStreamAccountD3(String casemanageId){
		String whichTable = caseManageService.getWhichTable(casemanageId);
		//第一步 封装所有的交易主体
		String accountBodySql = "select distinct BODY_TYPE,ACCOUNT_BODY_CARD, (case when account_body is not null then account_body else account_body_card end) as ACCOUNT_BODY from "+whichTable+" where is_delete=0 and casemanage_id='"+casemanageId+"' and ACCOUNT_BODY_CARD is not null ";
		List<Map<String, Object>> bodys = jdbcTemplate.queryForList(accountBodySql.toString());

		Map<String, Object> hashMap = new HashMap<>();
		List<Map<String, Object>> nodes = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> links = new ArrayList<Map<String, Object>>();
		List<String> nodeNameList = new ArrayList<String>();
		if(bodys!=null && bodys.size()>0) {
			for(int i=0;i<bodys.size();i++) {
				/** 封装交易主题D3 **/
				encapsulationAccountBodyD3(nodes, nodeNameList, bodys.get(i));
			}
		}
		
		//第二步，封装主体之间的交易信息
		String flowSql = "select (case\r\n" + 
				"         when a.account_body is not null then\r\n" + 
				"          a.account_body\r\n" + 
				"         else\r\n" + 
				"          a.account_body_card\r\n" + 
				"       end) as ACCOUNT_BODY,\r\n" + 
				"       b.account_body as ACCOUNT_RIVAL,\r\n" + 
				"       count(1) as TOTALNUMBER,\r\n" + 
				"       sum(abs(a.account_money)) as TOTALMONEY\r\n" + 
				"  from "+whichTable+" a,\r\n" + 
				"       (select distinct (case\r\n" + 
				"                          when account_body is not null then\r\n" + 
				"                           account_body\r\n" + 
				"                          else\r\n" + 
				"                           account_body_card\r\n" + 
				"                        end) as account_body,\r\n" + 
				"                        account_body_card\r\n" + 
				"          from "+whichTable+" where casemanage_id='"+casemanageId+"' and is_delete=0) b\r\n" + 
				" where a.account_rival_card = b.account_body_card and a.casemanage_id='"+casemanageId+"' and is_delete=0 \r\n" + 
				" group by (case\r\n" + 
				"            when a.account_body is not null then\r\n" + 
				"             a.account_body\r\n" + 
				"            else\r\n" + 
				"             a.account_body_card\r\n" + 
				"          end),\r\n" + 
				"          b.account_body\r\n" + 
				"";
		List<Map<String, Object>> flowList = jdbcTemplate.queryForList(flowSql.toString());
		/** 封装交易主体交易对手line start**/
		if(flowList!=null && flowList.size()>0) {
			for (int i=0;i<flowList.size();i++){
				Map<String, Object> map = flowList.get(i);
				HashMap<String, Object> link = new HashMap<>();
				String nodeName = map.get("ACCOUNT_BODY")==null?"":map.get("ACCOUNT_BODY").toString();
				String nodeName2 = map.get("ACCOUNT_RIVAL")==null?"":map.get("ACCOUNT_RIVAL").toString();
				for (Map<String, Object> m:nodes){
					if (nodeName2.equals((String)m.get("name"))){
						link.put("node1",m.get("id"));
					}
					if (nodeName.equals((String)m.get("name"))){
						link.put("node2",m.get("id"));
					}
					link.put("name","交易"+map.get("TOTALNUMBER")+"次,共交易"+map.get("TOTALMONEY")+"元");
				}
				if(!link.keySet().contains("node1")||!link.keySet().contains("node2")) continue;
				links.add(link);
			}
		}
        /** 封装交易主体交易对手line end**/
		hashMap.put("links",links);
        hashMap.put("nodes",nodes);
		
        return hashMap;
    }
    
	
	
    /**
	 * 封装交易对手D3
	 * @param dealFlowdto
	 * @param selectedCards
	 * @param nodes
	 * @param nodeNameList
	 * @param map
	 */
	private void encapsulationAccountBodyD3(List<Map<String, Object>> nodes, List<String> nodeNameList, Map<String, Object> map) {
		String nodeName = map.get("ACCOUNT_BODY")==null?(map.get("ACCOUNT_BODY_CARD").toString()):(map.get("ACCOUNT_BODY").toString());
		String nodeCard = map.get("ACCOUNT_BODY_CARD")==null?"":map.get("ACCOUNT_BODY_CARD").toString();
		String isSpecial = (map.get("BODY_TYPE")!=null && "嫌疑人".equals(map.get("BODY_TYPE").toString()))?"1":"0";
		if (!nodeNameList.contains(nodeName)){
		    HashMap<String, Object> node = new HashMap<>();
		    node.put("id",UUID.randomUUID().toString());
		    node.put("name",nodeName);
		    node.put("card",nodeCard);
		    node.put("isSpecial", isSpecial);
	    	node.put("width",60);
	    	node.put("height",60);
		    // 判断账户名称是否包含企业或公司
		    if(com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeName) && com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeCard)) {
		    	if(nodeName.contains("企业") || nodeName.contains("公司") 
		    			|| nodeName.contains("支付宝") || nodeName.contains("财付通")
		    			|| nodeCard.length() == 15 || nodeCard.length() == 17 ) {
		    		if(isSpecial.equals("1")) {
		    			//嫌疑单位账户
		        		node.put("image","/plugins/d3/image/q2.png");
		    		}else {
		    			node.put("image","/plugins/d3/image/q1.png");
		    		}
		    	}else {
		    		if(isSpecial.equals("1")) {
		    			//嫌疑人账户
		        		node.put("image","/plugins/d3/image/r2.png");
		    		}else {
		    			node.put("image","/plugins/d3/image/r1.png");
		    		}
		    	}
		    }else {
		    	node.put("image","/plugins/d3/image/r1.png");
		    }
		    nodes.add(node);
		    nodeNameList.add(nodeName);
		}
	}
	

	public List<DealFlow> findByUuid(String caseManageId,String uuid){
		String whichTable = caseManageService.getWhichTable(caseManageId);
		String sb="select * from "+whichTable+" WHERE CASEMANAGE_ID= '"+caseManageId+"' AND UUID= '" +uuid+"'";
		List<DealFlow> query = jdbcTemplate.query(sb, new BeanPropertyRowMapper<DealFlow>(DealFlow.class));
		return query;
	}

	/**
	 * @Author 刘中华
	 * @Date 2019/3/28 11:13
	 * @Param jdbc分页查询
	 * @return
	 * @Description
	 **/
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageResult getDealFlowByPage(DealFlow dealFlow,Integer page, Integer size){
		String whichTable = caseManageService.getWhichTable(dealFlow.getCasemanageId());
		StringBuffer sb = new StringBuffer();
		sb.append("select "+returnDealFlowField() +"  from  "+whichTable);
		sb.append(" where 1=1 ");
		sb.append(" ");
		Query query = getEntityManager().createNativeQuery(sb.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(DealFlow.class));
		List<DealFlow> maps = query.getResultList();
		// 设置分页
		query.setFirstResult((page - 1) * size);
		query.setMaxResults(size);
		// 获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult pageResult = new PageResult(Long.valueOf(maps.size()), list);
		return pageResult;
	}

	//返回需要查询的字段
	public String returnDealFlowField(){
		StringBuffer sb=new StringBuffer();
		sb.append("  bill_id as billId ");
		sb.append(" ,account_body as accountBody ");
		sb.append(" ,account_body_card as accountBodyCard ");
		sb.append(" ,sign as sign ");
		sb.append(" ,trade_date as tradeDate ");
		sb.append(" ,account_rival as accountRival ");
		sb.append(" ,account_rival_number as accountRivalNumber ");
		sb.append(" ,account_rival_card as accountRivalCard ");
		sb.append(" ,account_money as accountMoney ");
		sb.append(" ,surplus_money as surplusMoney ");
		sb.append(" ,content_abstract as contentAbstract ");
		sb.append(" ,account_body_bank as accountBodyBank ");
		sb.append(" ,account_rival_bank as accountRivalBank ");
		sb.append(" ,account_address as accountAddress ");
		sb.append(" ,account_mode as accountMode ");
		sb.append(" ,serial_number as serialNumber ");
		sb.append(" ,mac_address as macAddress ");
		sb.append(" ,ip_address as ipAddress ");
		sb.append(" ,currency as currency ");
		sb.append(" ,remarks as remarks ");
		sb.append(" ,template_tag as templateTag ");
		sb.append(" ,casemanage_id as casemanageId ");
		sb.append(" ,create_id as createId ");
		sb.append(" ,teller_number as tellerNumber ");
		sb.append(" ,trading_organization_number as tradingOrganizationNumber ");
		sb.append(" ,institution_number as institutionNumber ");
		sb.append(" ,create_time as createTime ");
		sb.append(" ,id_number as idNumber ");
		sb.append(" ,uuid as uuid ");
		sb.append(" ,body_type as bodyType ");

		return sb.toString();
	}
	
	@CacheEvict(value=CacheableConfig.BILL_SHOUYE, key="'getInStreamAccountD3'+#casemanageId")
	public void clearCache1(String casemanageId) {
	}
	
	@CacheEvict(value=CacheableConfig.BILL_SHOUYE, key="'getAccountNumberByCaseManageId'+#casemanageId")
	public void clearCache2(String casemanageId) {
	}
	
	@CacheEvict(value=CacheableConfig.BILL_SHOUYE, key="'findBillSummary'+#casemanageId")
	public void clearCache3(String casemanageId) {
	}
	
	@CacheEvict(value=CacheableConfig.BILL_SHOUYE, key="'findCommonAccountByCaseManagerId'+#casemanageId")
	public void clearCache4(String casemanageId) {
	}
	
	@CacheEvict(value=CacheableConfig.BILL_SHOUYE, key="'findTMoneyForByCaseManagerId'+#casemanageId")
	public void clearCache5(String casemanageId) {
	}
	
	 
    /**
     * 封装交易主题D3
     * @param maps
     * @param nodes
     * @param nodeNameList
     * @param i
     */
	private void encapsulationAccountRivalD3(List<Map<String, Object>> maps, List<Map<String, Object>> nodes,
			List<String> nodeNameList, int i) {
		Map<String, Object> map = maps.get(i);
		//封装交易对手 多循环一次是为了先封装交易主体，再封装对手
		String nodeName2 = map.get("ACCOUNT_RIVAL")==null?(map.get("ACCOUNT_RIVAL_CARD").toString()):(map.get("ACCOUNT_RIVAL").toString());
		String nodeCard2 = map.get("ACCOUNT_RIVAL_CARD")==null?"":map.get("ACCOUNT_RIVAL_CARD").toString();
		if (!nodeNameList.contains(nodeName2)){
		    HashMap<String, Object> node = new HashMap<>();
		    node.put("id",UUID.randomUUID().toString());
		    node.put("name",nodeName2);
		    node.put("card",nodeCard2);
		    node.put("isSpecial", "0");
			node.put("width",60);
			node.put("height",60);
			// 判断账户名称是否包含企业或公司
		    if(com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeName2) && com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeCard2)) {
		    	if(nodeName2.contains("企业") || nodeName2.contains("公司") 
		    			|| nodeName2.contains("支付宝") || nodeName2.contains("财付通")
		    			|| nodeCard2.length() == 15 || nodeCard2.length() == 17) {
		    		node.put("image","/plugins/d3/image/q1.png");
		    	}else {
		    		node.put("image","/plugins/d3/image/r1.png");
		    	}
		    }else {
		    	node.put("image","/plugins/d3/image/r1.png");
		    }
			
		    nodes.add(node);
		    nodeNameList.add(nodeName2);
		}
	}
	
	/**
	 * 封装交易对手D3
	 * @param dealFlowdto
	 * @param selectedCards
	 * @param nodes
	 * @param nodeNameList
	 * @param map
	 */
	private void encapsulationAccountBodyD3(DealFlowVo dealFlowdto, List<String> selectedCards,
			List<Map<String, Object>> nodes, List<String> nodeNameList, Map<String, Object> map) {
		String nodeName = map.get("ACCOUNT_BODY")==null?(map.get("ACCOUNT_BODY_CARD").toString()):(map.get("ACCOUNT_BODY").toString());
		String nodeCard = map.get("ACCOUNT_BODY_CARD")==null?"":map.get("ACCOUNT_BODY_CARD").toString();
		String isSpecial = (map.get("BODY_TYPE")!=null && "嫌疑人".equals(map.get("BODY_TYPE").toString()))?"1":"0";
		if (!nodeNameList.contains(nodeName)){
		    HashMap<String, Object> node = new HashMap<>();
		    node.put("id",UUID.randomUUID().toString());
		    node.put("name",nodeName);
		    node.put("card",nodeCard);
		    node.put("isSpecial", isSpecial);
		    if("-1".equals(dealFlowdto.getCheckedAccount()) || selectedCards.contains("'"+nodeCard+"'")) {
		    	node.put("width",100);
		    	node.put("height",100);
		    }else {
		    	node.put("width",60);
		    	node.put("height",60);
		    }
		    // 判断账户名称是否包含企业或公司
		    if(com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeName) && com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeCard)) {
		    	if(nodeName.contains("企业") || nodeName.contains("公司") 
		    			|| nodeName.contains("支付宝") || nodeName.contains("财付通")
		    			|| nodeCard.length() == 15 || nodeCard.length() == 17 ) {
		    		if(isSpecial.equals("1")) {
		    			//嫌疑单位账户
		        		node.put("image","/plugins/d3/image/q2.png");
		    		}else {
		    			node.put("image","/plugins/d3/image/q1.png");
		    		}
		    	}else {
		    		if(isSpecial.equals("1")) {
		    			//嫌疑人账户
		        		node.put("image","/plugins/d3/image/r2.png");
		    		}else {
		    			node.put("image","/plugins/d3/image/r1.png");
		    		}
		    	}
		    }else {
		    	node.put("image","/plugins/d3/image/r1.png");
		    }
		    nodes.add(node);
		    nodeNameList.add(nodeName);
		}
	}

}

